Case insensitive SQL SELECT query examples

SQL SELECT query FAQ: How do I issue SQL SELECT queries while ignoring case (i.e., case-insensitive queries, ignoring whether a string is uppercase or lowercase)?

Background

When I first started writing SQL queries I was using Postgresql, and used some of their custom regular expression capabilities to perform case-insensitive queries. That seemed like a good idea at the time, but when I tried to move my application to a MySQL database, a portion of my application no longer worked. After some debugging I remembered that I had used Postgreql-specific query capabilities, so I had to re-write that portion of the code to work with MySQL.

To save you some of that grief, the following examples show how to write case-insensitive SQL SELECT queries using SQL standard syntax. The queries shown should work with most, if not all, SQL 92 compliant databases.

A very important caveat

Please note one VERY IMPORTANT thing about the approach shown:

These queries force your database to perform a table scan. This means that if you have an index on the column that you’re searching, that index won’t be used. So if you have a billion records in your table, this approach will force the database to look at all one billion records. Obviously this is a very important caveat you need to be aware of.

Case insensitive SQL SELECT: Use upper or lower functions

The SQL standard way to perform case insensitive queries is to use the SQL upper or lower functions, like this:

select * from users where upper(first_name) = 'FRED';

or this:

select * from users where lower(first_name) = 'fred';

As you can see, the pattern is to make the field you're searching into uppercase or lowercase, and then make your search string also be uppercase or lowercase to match the SQL function you’ve used. This works with all SQL databases I’ve used, including Postgresql, MySQL, and SQL Server.

SQL LIKE queries

You can use the same technique to make your SQL LIKE queries case insensitive as well. Here’s how you use the uppercase function with a SQL LIKE query:

select * from users where upper(first_name) like '%AL%';

and here’s the same case insensitive SQL LIKE query using the SQL lowercase function:

select * from users where lower(first_name) like '%al%';

Another possible approach

I heard from one developer who said that their company kept two copies of some of their string data where performance was important. In one column they stored the original string from the user, and in a second column they converted that string to lowercase and stored it there. Then, when the user needed to search for case-insensitive data, they would search the second column.

So basically:

• The first column is named something like raw_data
• The second column is named lowercase_data
• Right before you perform the search, you (a) convert the new search string to lowercase, then (b) search the lowercase_data column with that lowercase string

Note that I would only use this technique if you had to search a ton of rows and want to avoid the table-scan issue I mentioned earlier. The benefit of this solution is faster, indexed searches, and the drawbacks are duplicating the data, and needing to keep that duplicated data in sync. As always, you’ll want to test something like this to see if the increased search performance is worth it.

Summary

I hope these case insensitive SQL SELECT query examples are helpful. Again, the trick is to convert whatever you’re searching for to uppercase or lowercase using the SQL upper and lower functions, and then make your search string match that case.