SQL Server: Querying Case Insensitive Data With Case Sensitive Filters

Today, I have faced a very simple but still annoying problem. I was asked by a client that queries are not working correctly with given WHERE clause. Actually database was design as case insensitive but SOMEHOW on application side there were some business logics which client needs case sensitive inputs (Just like password) and comparisons.  

Here is an example for easy understanding. We have following simple table and data.

USE AdventureWorks

GO

CREATE TABLE #CaseInSensitive

(cisId INT identity(1,1), cisText varchar(50))

INSERT INTO #CaseInSensitive (cisText)

SELECT 'Abc'

UNION ALL

SELECT 'ABc'

UNION ALL

SELECT 'ABC'

UNION ALL

SELECT 'AbC'

If we need all records where column cisText value is ‘ABC’ then here is simple query

SELECT * fROM #CaseInSensitive

WHERE cisText = 'ABC'

Ops. We got all four records as output but we need only one records where cisText = ‘ABC’. But at the time of table creation we have not set any case sensitive collation. Don’t worry; here is a query which can help us.

SELECT * fROM #CaseInSensitive

WHERE cisText COLLATE SQL_Latin1_General_CP1_CS_AS = 'ABC'

During table design we can also make a column case sensitive

CREATE TABLE #CaseSensitive (csId INT identity(1,1), csText varchar(50) COLLATE SQL_Latin1_General_CP1_CS_AS)