Tuesday, March 22, 2011

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)

3 comments:

  1. During table design we can also make a column case sensitive

    ReplyDelete
  2. This is why it should not be done like this on any sizeable data sets!

    http://www.stev.org/post/2011/03/31/MSSQL-Query-case-insensitive-data.aspx

    ReplyDelete
  3. If we are working an environment where we can't deploy collation changes, we can cast varchars to varbinary, making sure a case sensitive query is performed

    http://methodsoftware.blogspot.com.es/2012/10/t-sql-case-sensitive-queries.html

    Greetings

    ReplyDelete

All suggestions are welcome