Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Jun 2006
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    [POSTGRES] SQL Noob: LIKE and ignore case?

    Is there anyway to use the like clause but have it ignore upper/lowercase differances?
    I see it is possible to upper/lower case the letters themselves before doing the expression, but that is kind of useless if you don't know what the case is inside the database more then likely mixed case.

    (ps, im actually using postgres..but I'm assuming there must be standard sql way to do this).

    In the long run I will probably need a better keyword indexing strategy, but this is just to get basic 'search' working for now on my project.

    thanx
    Dano
    Last edited by guelphdad; 08-16-2006 at 08:20 PM. Reason: change title and move to correct forum

  • #2
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Unless the column is cast as binary or given a case sensetive collation, LIKE is case insensitive in MySQL. No idea how Postgres handles case, though. You should ask a mod to move this to the Other Databases forum.

  • #3
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    YOu coerce both sides of the equation to be the same:
    SELECT * FROM table WHERE lower(column) LIKE lower('StringFromCode%');

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    You could always use the to_upper() or to_lower() function, assuming Postgres has comparable functions.

  • #5
    New Coder
    Join Date
    Jun 2006
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ding! excelent the lower() function applied to the column name worked perfectly, many thanx.

  • #6
    Regular Coder
    Join Date
    Aug 2004
    Location
    The US of A
    Posts
    767
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Actually, LIKE is case sensitive in PostGreSQL (unless they changed it on me). I've always used ILIKE for case insensitive queries.

    Maybe it's just me. Maybe I shouldn't post when I first wake up. Who knows.

  • #7
    Regular Coder ralph l mayo's Avatar
    Join Date
    Nov 2005
    Posts
    951
    Thanks
    1
    Thanked 31 Times in 29 Posts
    judging from a few tests on tables I have handy, ILIKE is going to marginally faster than explicitly coercing to lowercase on both sides.


  •  

    Posting Permissions

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