>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)
Advertisements

Posted on March 22, 2011, in Sql Server 2005, Sql Server 2008, TSQL Tips n Tricks. Bookmark the permalink. 3 Comments.

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

  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

  3. We got all four records as output but we need only one record

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: