SQL 'LIKE' syntax

I'm writing an application that needs to work on both mysql and postgresql. I have to u开发者_开发知识库se like to compare some values.

In mysql LIKE it's case insensitive. In postgresql LIKE it's case sensitive and ILIKE it's case insensitive.

What is the best way to make a solid query that works on both in case the match has to be case insensitive ?

Does PDO have a solution for this ?

The easiest way to ensure a case-insensitive LIKE is to use something like one of these:

LOWER(column_name) LIKE LOWER(pattern)
UPPER(column_name) LIKE UPPER(pattern)

Or you can up-case/down-case the pattern outside the SQL and just use:

LOWER(column_name) LIKE down_cased_pattern
UPPER(column_name) LIKE up_cased_pattern

I tend to use LOWER out of habit as lower case is easier to read and hence easier to debug.

This is the job of a Database Abstraction Layer.

PDO does not do what you're looking for but there are a few PHP DALs if you google for them.

PDO does not provide a database abstraction; it doesn't rewrite SQL or emulate missing features. You should use a full-blown abstraction layer if you need that facility.