SQL 'LIKE' syntax

Question

I'm writing an application that needs to work on both mysql and postgresql. I have to use 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 ?

Answer 1

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.

Answer 2

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.