Case sensitivity in databases

Recently, I had to clean up some SQL to be case-insensitive. It was originally written to be run in MySQL which defaults to case-insensitive searches but can be set otherwise. Now, however, the SQL run is being run in PostgreSQL which always performs case-sensitive searches.

Here's an example using Ruby on Rails's Active Record:

Suppose that a name is stored in the providers table as "SMITH".

Below, the set collection will be empty because Smith is not like SMITH.
@search_field = "Smith"
set = Provider.find(:all, :conditions => "name LIKE '%#{@search_field}%'")


But this yields the expected results:
@search_field = "Smith"
set = Provider.find(:all, :conditions => ["LOWER(name) LIKE LOWER(?)", "%#{@search_field}%"])

The reason the second example works is because it uses the database's LOWER() string function to change Smith to smith and SMITH to smith.

And, as it so happens, the UPPER() and LOWER() string functions both exist and work identically in MySQL and PostgreSQL so the code above is portable.