Case insensitive SQL SELECT query examples

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

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 that there is one VERY IMPORTANT thing to say 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.

I don’t post comments from people who use derogatory language, but as one commenter wrote in between insults, “using a function to convert an entire column of data requires a table scan and should be avoided in a WHERE clause.”

SQL SELECT case insensitive queries - 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 SELECT case insensitive queries - 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%';

SQL SELECT case insensitive queries - 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.

Add new comment

The content of this field is kept private and will not be shown publicly.

Anonymous format

  • Allowed HTML tags: <em> <strong> <cite> <code> <ul type> <ol start type> <li> <pre>
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.