SQL Syntax 'LIKE'

Question

I am writing an application that should work with both mysql and postgresql. I need to use like to compare some values.

In mysql like it is case insensitive. In postgresql like it is case sensitive and ILIKE is case insensitive.

What is the best way to make a reliable query that works on both, if the match should be case insensitive?

Does PDO have a solution for this?

Answer 1

The easiest way to provide case-insensitive LIKE is to use something like this:

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

Or you can use case-town / down-case pattern outside of SQL and just use:

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

I usually use LOWER out of habit, as a simpler option is easier to read and therefore easier to debug.

Answer 2

This is a database abstraction layer job.

PDO does not do what you are looking for, but there are several PHP DALs if you google them .

PDO does not provide database abstraction; It does not rewrite SQL or emulate missing functions. You should use a full-blown abstraction layer if you need this tool.