By Alvin Alexander. Last updated: December 30, 2023
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 SQLstandard syntax. The queries shown should work with most, if not all, SQL92 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.
FAQs
To do a case-insensitive comparison, use the ILIKE keyword; e.g., column ILIKE 'aBc' and column ILIKE 'ABC' both return TRUE for 'abc' . In contrast, MySQL and MS SQL Server have case-insensitive behaviors by default. This means WHERE column = 'abc' returns TRUE for e.g., 'abc' , 'ABC' , or 'aBc' .
How do I select without case-sensitive in SQL? ›
Case insensitive SQL SELECT: Use upper or lower functions
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.
How do I force case-sensitive in SQL? ›
For case-sensitive searches in SQL Server, use COLLATE keyword with the case sensitive collation “SQL_Latin1_General_CP1_CS_AS” followed by the LIKE or = comparisons as usual.
How do I remove case-sensitive in SQL? ›
To remove the case-sensitivity,
Ensure that the SQL Server is defined as case-insensitive, otherwise these changes will not help. SELECT CONVERT (varchar, DATABASEPROPERTYEX('database_name','collation')); 2. Then remove all entries of this within the SQL Schema holder through the Database Admin utility.
How to set case-insensitive in SQL Server? ›
Which means, SQL Server considers the uppercase and lowercase versions of letters to be identical for sorting purposes. You can explicitly select case insensitivity by specifying _CI. Distinguishes between accented and unaccented characters. For example, “a” is not equal to “ấ”.
What is the find command for case-insensitive? ›
By passing the name of the file using the -name flag, the find command searches and returns the location of the file. But remember the -name flag performs a case-sensitive search. If you are looking to do a case-insensitive search, you can use the -iname flag instead.
How do I ignore case sensitivity? ›
To ignore case in JavaScript, you can use one of the following methods: The toLowerCase() or toUpperCase() methods: These methods convert a string to all lowercase or uppercase characters, allowing you to perform case-insensitive comparisons.
How do I ignore case-sensitive in less? ›
Note: By default, searching in less is case-sensitive. Ignore case sensitivity by specifying the -I option or pressing the I key within less .
What can I use instead of case in SQL? ›
Using the IIF function
With an IIF function, we largely replace a lot of the syntactical sugar from the CASE statement with a few simple comma-seperators to differentiate our arguments. All told, both CASE and IIF get the same job done, but if given the choice, IIF will generally be much simpler to use.
Why isn't SQL case-sensitive? ›
At the time SQL was conceived, most languages that were in use had at one point been written on punch cards which were case-insensitive. This probably influenced the design of SQL. Syntax highlighting didn't exist either, so case was probably a convenient way to distinguish between keywords and data.
In MSSQL, table and column names are not case sensitive on windows as MSSQL represents databases as a file and directories on the server. As a result of this case sensitivity of databases depends upon how the operating system works.
Is MySQL query case-sensitive? ›
Table names are stored in lowercase on disk and name comparisons are not case-sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases.
How do I know if SQL is case sensitive? ›
SQL Server is, by default case insensitive; however, it is possible to create a case sensitive SQL Server database and even to make specific table columns case sensitive. The way to determine a database or database object is by checking its “COLLATION” property and look for “CI” or “CS” in the result.
Is SQL always case sensitive? ›
By default, SQL Server names are case insensitive. However, you can create a case sensitive SQL Server database by changing the COLLATION property. In PostgreSQL, object names are case insensitive. By default, the AWS Schema Conversion Tool (AWS SCT) uses object names in lowercase for PostgreSQL.
Is SQL replace case sensitive? ›
Since the replace() function search is case sensitive, it will not consider 'Sql' the same as 'SQL. ' Thus, for the server, there is no SQL in the original string.
How do you make a string case-insensitive? ›
To ignore case in JavaScript, you can use one of the following methods: The toLowerCase() or toUpperCase() methods: These methods convert a string to all lowercase or uppercase characters, allowing you to perform case-insensitive comparisons.
Which operator is case-insensitive in SQL? ›
By default, the collation is case-insensitive, so the LIKE operator will perform a case-insensitive search. However, if the column has a case-sensitive collation, the LIKE operator will be case-sensitive.
Is SQL statement case-insensitive? ›
The SQL language specification says that "SQL language characters" (which are used for identifiers and SQL keywords) are case-insensitive. If SQL were case-sensitive, it would not comply with the language standard. So you would either have to change the SQL standard, or else have a rebellious streak.
Would you make a case-insensitive query in MySQL? ›
The default collations used by SQL Server and MySQL do not distinguish between upper and lower case letters—they are case-insensitive by default.
