...

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



DanoMano
08-16-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.

thanx
Dano

Kid Charming
08-16-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.

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

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

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

Kurashu
08-17-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
08-31-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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum