Case-Insensitive Search in SQL (2024)

Ignoring the case in a where clause is very simple. You can, for example, convert both sides of the comparison to all caps notation:

SELECT first_name, last_name, phone_number FROM employees WHERE UPPER(last_name) = UPPER('winand')

Regardless of the capitalization used for the search term or the LAST_NAME column, the UPPER function makes them match as desired.

Note

Another way for case-insensitive matching is to use a different “collation”. The default collations used by SQL Server and MySQL do not distinguish between upper and lower case letters—they are case-insensitive by default.

The logic of this query is perfectly reasonable but the execution plan is not:

DB2

Explain Plan------------------------------------------------------ID | Operation | Rows | Cost 1 | RETURN | | 690 2 | TBSCAN EMPLOYEES | 400 of 10000 ( 4.00%) | 690Predicate Information 2 - SARG ( UPPER(Q1.LAST_NAME) = 'WINAND')

Oracle

----------------------------------------------------| Id | Operation | Name | Rows | Cost |----------------------------------------------------| 0 | SELECT STATEMENT | | 10 | 477 ||* 1 | TABLE ACCESS FULL| EMPLOYEES | 10 | 477 |----------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(UPPER("LAST_NAME")='WINAND')

PostgreSQL

 QUERY PLAN------------------------------------------------------ Seq Scan on employees (cost=0.00..1722.00 rows=50 width=17) Filter: (upper((last_name)::text) = 'WINAND'::text)

It is a return of our old friend the full table scan. Although there is an index on LAST_NAME, it is unusable—because the search is not on LAST_NAME but on UPPER(LAST_NAME). From the database’s perspective, that’s something entirely different.

Support My Work

I offer SQL training, tuning and consulting. Buying my book “SQL Performance Explained” (from €9.95) also supports my work on this website.

This is a trap we all might fall into. We recognize the relation between LAST_NAME and UPPER(LAST_NAME) instantly and expect the database to “see” it as well. In reality the optimizer’s view is more like this:

SELECT first_name, last_name, phone_number FROM employees WHERE BLACKBOX(...) = 'WINAND'

The UPPER function is just a black box. The parameters to the function are not relevant because there is no general relationship between the function’s parameters and the result.

Tip

Replace the function name with BLACKBOX to understand the optimizer’s point of view.

Compile Time Evaluation

The optimizer can evaluate the expression on the right-hand side during “compile time” because it has all the input parameters. The Oracle execution plan (“Predicate Information” section) therefore only shows the upper case notation of the search term. This behavior is very similar to a compiler that evaluates constant expressions at compile time.

To support that query, we need an index that covers the actual search term. That means we do not need an index on LAST_NAME but on UPPER(LAST_NAME):

CREATE INDEX emp_up_name ON employees (UPPER(last_name))

An index whose definition contains functions or expressions is a so-called function-based index (FBI). Instead of copying the column data directly into the index, a function-based index applies the function first and puts the result into the index. As a result, the index stores the names in all caps notation.

The database can use a function-based index if the exact expression of the index definition appears in an SQL statement—like in the example above. The execution plan confirms this:

DB2

Explain Plan-------------------------------------------------------ID | Operation | Rows | Cost 1 | RETURN | | 13 2 | FETCH EMPLOYEES | 1 of 1 (100.00%) | 13 3 | IXSCAN EMP_UP_NAME | 1 of 10000 ( .01%) | 6Predicate Information 3 - START ( UPPER(Q1.LAST_NAME) = 'WINAND') STOP ( UPPER(Q1.LAST_NAME) = 'WINAND')

The query was changed to WHERE UPPER(last_name) = 'WINAND' (no UPPER on the right hand side) to get the expected result. When using UPPER('winand'), the optimizer does a gross misestimation and expects 4% of the table rows to be selected. This causes the optimizer to ignore the index and do a TBSCAN. See “Full Table Scan” to see why that might make sense.

Oracle

--------------------------------------------------------------|Id |Operation | Name | Rows | Cost |--------------------------------------------------------------| 0 |SELECT STATEMENT | | 100 | 41 || 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 100 | 41 ||*2 | INDEX RANGE SCAN | EMP_UP_NAME | 40 | 1 |--------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access(UPPER("LAST_NAME")='WINAND')

PostgreSQL

 QUERY PLAN------------------------------------------------------------Bitmap Heap Scan on employees (cost=4.65..178.65 rows=50 width=17) Recheck Cond: (upper((last_name)::text) = 'WINAND'::text) -> Bitmap Index Scan on emp_up_name (cost=0.00..4.64 rows=50 width=0) Index Cond: (upper((last_name)::text) = 'WINAND'::text)

It is a regular INDEX RANGE SCAN as described in Chapter1. The database traverses the B-tree and follows the leaf node chain. There are no dedicated operations or keywords for function-based indexes.

Warning

Sometimes ORM tools use UPPER and LOWER without the developer’s knowledge. Hibernate, for example, injects an implicit LOWER for case-insensitive searches.

The execution plan is not yet the same as it was in the previous section without UPPER; the row count estimate is too high. It is particularly strange that the optimizer expects to fetch more rows from the table than the INDEX RANGE SCAN delivers in the first place. How can it fetch 100 rows from the table if the preceding index scan returned only 40 rows? The answer is that it can not. Contradicting estimates like this often indicate problems with the statistics. In this particular case it is because the Oracle database does not update the table statistics when creating a new index (see also “Oracle Statistics for Function-Based Indexes”).

Oracle Statistics for Function-Based Indexes

The Oracle database maintains the information about the number of distinct column values as part of the table statistics. These figures are reused if a column is part of multiple indexes.

Statistics for a function-based index (FBI) are also kept on table level as virtual columns. Although the Oracle database collects the index statistics for new indexes automatically (since release 10g), it does not update the table statistics. For this reason, the Oracle documentation recommends updating the table statistics after creating a function-based index:

After creating a function-based index, collect statistics on both the index and its base table using the DBMS_STATS package. Such statistics will enable Oracle Database to correctly decide when to use the index.
— Oracle Database SQL Language Reference

My personal recommendation goes even further: after every index change, update the statistics for the base table and all its indexes. That might, however, also lead to unwanted side effects. Coordinate this activity with the database administrators (DBAs) and make a backup of the original statistics.

After updating the statistics, the optimizer calculates more accurate estimates:

Oracle

--------------------------------------------------------------|Id |Operation | Name | Rows | Cost |--------------------------------------------------------------| 0 |SELECT STATEMENT | | 1 | 3 || 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 3 ||*2 | INDEX RANGE SCAN | EMP_UP_NAME | 1 | 1 |--------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access(UPPER("LAST_NAME")='WINAND')

PostgreSQL

 QUERY PLAN---------------------------------------------------------- Index Scan using emp_up_name on employees (cost=0.00..8.28 rows=1 width=17) Index Cond: (upper((last_name)::text) = 'WINAND'::text)

As the row count estimate has decreased—from 50 in the example above down to 1 in this execution plan—the query planner prefers to use the simpler Index Scan operation.

Note

The so-called “extended statistics” on expressions and column groups were introduced with Oracle release 11g.

Although the updated statistics do not improve execution performance in this case—the index was properly used anyway—it is always a good idea to check the optimizer’s estimates. The number of rows processed for each operation (cardinality estimate) is a particularly important figure that is also shown in SQL Server and PostgreSQL execution plans.

Tip

AppendixA, “Execution Plans”, describes the row count estimates in the execution plans of other databases.

SQL Server and MySQL do not support function-based indexes as described but both offer a workaround via computed or generated columns. To make use of this, you have to first add a generated column to the table that can be indexed afterwards:

MySQL

Since MySQL 5.7 you can index a generated columns as follows:

ALTER TABLE employees ADD COLUMN last_name_up VARCHAR(255) AS (UPPER(last_name));

CREATE INDEX emp_up_name ON employees (last_name_up);

SQL Server

ALTER TABLE employees ADD last_name_up AS UPPER(last_name)

CREATE INDEX emp_up_name ON employees (last_name_up)

SQL Server and MySQL are able to use this index whenever the indexed expression appears in the statement. In some simple cases, SQL Server and MySQL can use this index even if the query remains unchanged. Sometimes, however, the query must be changed to refer to the name of the new columns in order to use the index. Always check the execution plan in case of doubt.

FAQs

What is case-insensitive search in SQL? ›

By default, MySQL uses a case-insensitive collation for string comparisons. This means that when MySQL compares two strings, it considers 'A' and 'a' to be the same. For example, if a record in database is the name 'John', and user run a search for 'john', MySQL will return the record with the name 'John'.

Read On ›

How to query without case-sensitive in SQL? ›

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' .

Discover More Details ›

How do I make my case-insensitive search? ›

Use wildcard() The wildcard() function allows for searching events using a wildcard pattern search. Using the ignoreCase parameter the search can be performed ignoring the case of the string. Will search for the word error ignoring the case, matching Error and ERROR and other variants.

Is like search 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.

See Details ›

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.

Find Out More ›

How to remove case-sensitive in SQL? ›

You can turn off table name case sensitivity in MySQL by setting the parameter lower_case_table_names to 1.

Tell Me More ›

Which function in SQL is case-sensitive? ›

SQL is often case-sensitive when it comes to string comparisons, meaning that it distinguishes between uppercase and lowercase characters. For example, 'A' and 'a' are considered distinct values in a case-sensitive SQL environment.

Show Me More ›

What is an accent-insensitive search? ›

We often need our text search to be agnostic of accent marks. Accent-insensitive search, also called diacritics-agnostic search, is where search results are the same for queries that may or may not contain Latin characters such as à, è, Ê, ñ, and ç.

Explore More ›

How to search case-insensitive 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 .

How do I ignore case-sensitive strings? ›

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.

Show Me More ›

How do you make a code not case-sensitive? ›

You can achieve this using the toLowerCase() or toUpperCase() method in JavaScript. This code will output "The strings are equal!" because we're converting both string1 and string2 to lowercase before comparing them. By converting them to lowercase, we've made the comparison case-insensitive.

Read The Full Story ›

Are SQL queries case-insensitive? ›

Are SQL Keywords Case-Sensitive? The straightforward answer is that SQL is generally case-insensitive. That means you could write your SQL keywords in any case and the database engine will interpret them correctly. All of these will produce the same result, regardless of the case used in the keywords.

See Details ›

How to ignore case statement in SQL? ›

Using LOWER() and UPPER() functions for case in-sensitive that is ignoring the case in queries.

Get More Info Here ›

Is like query case-insensitive in MySQL? ›

When searching for partial strings in MySQL with LIKE you will match case-insensitive by default*. If you want to match case-sensitive, you can cast the value as binary and then do a byte-by-byte comparision vs. a character-by-character comparision. The only thing you need to add to your query is BINARY .

What is case-sensitive searching? ›

In computers, case sensitivity defines whether uppercase and lowercase letters are treated as distinct (case-sensitive) or equivalent (case-insensitive). For instance, when users interested in learning about dogs search an e-book, "dog" and "Dog" are of the same significance to them.

What is insensitive cursor in SQL? ›

INSENSITIVE. Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb .

View Details ›

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.

Which databases are case-insensitive? ›

For one thing, databases vary considerably in how they handle text; for example, while some databases are case-sensitive by default (e.g. Sqlite, PostgreSQL), others are case-insensitive (SQL Server, MySQL).

Learn More ›