[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

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.

YOu coerce both sides of the equation to be the same:
SELECT * FROM table WHERE lower(column) LIKE lower('StringFromCode%');

You could always use the to_upper() or to_lower() function, assuming Postgres has comparable functions.

Ding! excelent the lower() function applied to the column name worked perfectly, many thanx. :)

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.

judging from a few tests on tables I have handy, ILIKE is going to marginally faster than explicitly coercing to lowercase on both sides.