How to Do Case Insensitive Search? – Interview Question of the Week #267

Question: How do you search without considering letter case when the column is case-sensitive? Apply an appropriate case-insensitive collation to the comparison.

I usually get the opposite question: how to make a search case-sensitive. During a Comprehensive Database Performance Health Check, the database was case-sensitive but a particular search needed to ignore case. We chose an expression-level collation rather than changing the database.

DECLARE @CaseSensitive table
    (Col1 varchar(100) COLLATE SQL_Latin1_General_CP1_CS_AS);
INSERT @CaseSensitive (Col1) VALUES ('ABC'), ('abc'), ('aBc');

SELECT Col1 FROM @CaseSensitive WHERE Col1 = 'AbC';

SELECT Col1
FROM @CaseSensitive
WHERE Col1 COLLATE SQL_Latin1_General_CP1_CI_AS = 'AbC'
ORDER BY Col1 COLLATE Latin1_General_100_BIN2;

The first search returns no rows. None of the three stored spellings is exactly AbC under the column’s case-sensitive collation.

The second search returns all three: ABC, aBc and abc. The explicit CI collation makes upper- and lowercase equivalent for this comparison. It doesn’t rewrite the stored strings or change the column’s collation.

Choose the comparison you actually need

Collations also govern accents and other language rules. This example keeps AS, so it remains accent-sensitive. A case-insensitive comparison isn’t automatically an accent-insensitive one.

For a frequent search over a large table, review the execution plan. Applying a different collation to an indexed column can affect how the index is used. Choose the appropriate column or indexed expression design for the workload rather than adding conversions to every query without checking.

My earlier examples cover case-sensitive searches and case-sensitive queries with COLLATE. The same choice works in the other direction when the business requirement calls for it.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.