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%';

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.