Results 1 to 13 of 13

Thread: Search For

  1. #1
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Question Unanswered: Search For

    Can you please show or help me how I can do the following search but make it case insensitive? I did a Google search and found the LIKE condition however I can't find an example as shown below.

    Code:
    SELECT * FROM dp_user WHERE user_name = 'Carlos';
    Empty set (0.00 sec)
    I want to be able to find either 'Carlos' or 'carlos' in my SQL statement but I can't find anything on Google that shows me how to correct my SQL statement...

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which database system are you using?

    you posted in the ANSI SQL forum -- ANSI SQL is the "standard sql" language

    often, ANSI SQL is not suficient to solve specific issues, such as case sensitivity
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    Either use the LOWER function, or compare using a case-insensitive collation.

  4. #4
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    [doube-post]

  5. #5
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Quote Originally Posted by r937 View Post
    which database system are you using?

    you posted in the ANSI SQL forum -- ANSI SQL is the "standard sql" language

    often, ANSI SQL is not suficient to solve specific issues, such as case sensitivity
    I'm using PostgreSQL but assumed it was just something I could use ANSI SQL for.

  6. #6
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    Have you tried

    ...where lower(user_name) = 'carlos'...
    ?

    Or ...where user_name collate english_ci_ai = 'Carlos'...?

    I don't know any PostgreSQL's collation names, just wrote english_ci_ai as an example.

  7. #7
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Quote Originally Posted by JarlH View Post
    Have you tried

    ...where lower(user_name) = 'carlos'...
    ?

    Or ...where user_name collate english_ci_ai = 'Carlos'...?
    I just tried the following and looks like it worked:

    Code:
    ide=# SELECT id, fname, lname, email, dob, title FROM users WHERE LOWER(fname) = 'carlos';
    
     id | fname  |  lname  |        email        |    dob     |         title          
    ----+--------+---------+---------------------+------------+------------------------
      1 | Carlos | Mennens | carlos@iamghost.org | 1979-05-25 | Database Administrator
    (1 row)

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

  9. #9
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Now suppose I was trying to find a list of 'titles' which all contained the word 'administrator' in any case? I tried using the '%' to fill the gaps but it didn't work.

    Code:
    ide=# SELECT title FROM users;
              title           
    --------------------------
     Database Administrator
     Linux Administrator
    So I tried the following and I'm obviously missing something:

    Code:
    ide=# SELECT title FROM users WHERE LOWER(title) = '%administrator';
     title 
    -------
    (0 rows)
    Sorry for my ignorance or stupidity but I'm just trying to learn and document as much as I can learn.

  10. #10
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    So basically what this tells me is what I learn from that URL can't be copied over to database engines like MySQL or Oracle, correct?

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by CarlosinFL View Post
    ...WHERE LOWER(title) = '%administrator';
    you're thinking of LIKE here, not equals
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by CarlosinFL View Post
    So basically what this tells me is what I learn from that URL can't be copied over to database engines like MySQL or Oracle, correct?
    absoposilutely, yes, it cannot

    which is why questions asked in the ANSI SQL forum rarely get the final solution in actual ANSI SQL code
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Quote Originally Posted by r937 View Post
    which is why questions asked in the ANSI SQL forum rarely get the final solution in actual ANSI SQL code
    That's very interesting!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •