View Full Version : [POSTGRES] SQL Noob: LIKE and ignore case?

Aug 16th, 2006, 07:50 PM
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.


Kid Charming
Aug 16th, 2006, 08:04 PM
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.

Aug 16th, 2006, 08:15 PM
YOu coerce both sides of the equation to be the same:
SELECT * FROM table WHERE lower(column) LIKE lower('StringFromCode%');

Aug 16th, 2006, 08:16 PM
You could always use the to_upper() or to_lower() function, assuming Postgres has comparable functions.

Aug 17th, 2006, 04:25 PM
Ding! excelent the lower() function applied to the column name worked perfectly, many thanx. :)

Aug 17th, 2006, 06:37 PM
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.

ralph l mayo
Aug 31st, 2006, 06:31 AM
judging from a few tests on tables I have handy, ILIKE is going to marginally faster than explicitly coercing to lowercase on both sides.