8

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 ?

johnlemon
  • 20,761
  • 42
  • 119
  • 178

2 Answers2

12

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.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • 4
    "Q: Do all scripts have upper and lower case? A: No, as a matter of fact, most scripts do not have cases." (From the Unicode FAQ) In the general case, you can't count on a lowercase character to have an uppercase version. You also can't count on a lowercase character to map to a single uppercase character--some of them UPPER() to two characters. So lower() is safer, but just by a little bit. Don't you love living in a Unicode world? – Mike Sherrill 'Cat Recall' Jan 29 '11 at 23:00
  • 1
    @Catcall: A good example of a single lower case character mapping to two upper case characters comes from the exotic German language: `UPPER("ß")` is `"SS"`. I18N and L10N is such a happy fun time! Especially when you're dealing with JIS, SJIS, EUC, and Unicode Japanese at the same time without being able to read Japanese (been there, done that, glad that UTF-8 is pretty standard these days). – mu is too short Jan 30 '11 at 00:59
3

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.

Jeff Swensen
  • 3,513
  • 28
  • 52