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 did some pretty crazy things to try to ignore case in my text/string queries. Unfortunately for me, I first started with Postgresql, and used some of their custom regular expression capabilities. That seemed like a good idea at the time, but when I tried to move my application to a MySQL database, I paid a price in having to rewrite my case-insensitive SQL queries.

To save you some of that grief, here are several case insensitive SQL SELECT query examples. These SQL query examples use SQL standard syntax, so you can use them as shown with most, if not all, SQL 92 compliant databases.

SQL SELECT case insensitive queries - Use upper() or lower()

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 have been 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.

Post new comment

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