Interview Question of the Week #031 – How to do Case Sensitive SQL Query Search

Suppose a column contains CaseSearch, casesearch, CASESEARCH, and CaSeSeArCh. A user wants just the lowercase value. This is the small interview example I originally used, and it is clearer than starting with a tour of every SQL Server collation option.

Question: How do you make one SQL Server text search case sensitive?

Answer: Apply a case-sensitive collation to the comparison. First check the actual column collation, because a database or server default may differ from the column. Run the self-contained four-value example in a fresh query window without an existing #CaseSearch table:

CREATE TABLE #CaseSearch
(
    Column1 nvarchar(40) COLLATE Latin1_General_100_CI_AS NOT NULL
);

INSERT #CaseSearch (Column1) VALUES
    (N'CaseSearch'), (N'casesearch'),
    (N'CASESEARCH'), (N'CaSeSeArCh');

SELECT Column1
FROM #CaseSearch
WHERE Column1 = N'casesearch';

SELECT Column1
FROM #CaseSearch
WHERE Column1 COLLATE Latin1_General_100_CS_AS = N'casesearch';

The declared CI collation makes the first comparison case insensitive, so all four values qualify. The CS comparison distinguishes the capitalization and returns the lowercase value. This is the same idea as the original Latin1_General_CS_AS example, with an explicit case-insensitive starting column so the demonstration behaves consistently on different servers.

Collation also controls more than case. Accent behavior is a separate choice, and a binary collation answers a different matching question. Do not change a whole database default to solve one exceptional query. If this search becomes common and the column is indexed under a different collation, test the plan and consider a schema design that supports both the required comparison and an efficient access path.

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