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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
