Re: case insensitive search

You could use the UPPER function. Fortunately you're using PostgreSQL, which conforms with much of the ANSI standard. UPPER and LOWER are implementations of the fold functions required by ANSI SQL. This would be the most standard way to solve your problem (with the notable exception of support for this on MySQL, don't get me started on how brain dead MySQL is about string comparisons...though it is a tiny little bit better with its glaringly non-standard BINARY keyword). You could alternatively use the much older ~* syntax that PostgreSQL inherited from its ancestors. But that's completely non-standard, and there's only one reason to use it since PostgreSQL has the UPPER function and a LIKE predicate that very closely conforms to the ANSI SQL standard. That reason is if you want to use regex-like pattern specifiers instead of SQL-like pattern specifiers. If you're running a brand-spanking-new 7.1 (beta), you could use the popular (but non-standard, as far as I can tell--at least I couldn't find any mention of it in ANSI SQL '92 and I haven't looked at '99 yet) ILIKE. (Actually, '92 gives ILIKE reserved word status, but does not specify anything else for it.) To sum it up, try these: ... WHERE UPPER(blah) LIKE UPPER('%blah%') ... WHERE blah ~* '*blah*' (Note the regex-like pattern specifiers) ... WHERE blah ILIKE '%blah%' (Only on the next version) Doug At 06:36 PM 2/10/01 +1000, Cameron wrote: >im trying to do a case insensitive search in pgsql with a "WHERE blah >LIKE '%blah%'" > >the problem is that currently if something contains Blah not just blah >it wont return that. suggestion? i know i can suck out every row and >then eregi them but that is majorly slow and inefficent . . . > >Cameron