Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Case Sensitive Search on a Case Insensitive SQL Server

MSSQLTips author Jeremy Kadlec By:   |   Read Comments (14)   |   Related Tips: More > Query Optimization

Problem
Most SQL Server installations are installed with the default collation which is case insensitive.  This means that SQL Server ignores the case of the characters and treats the string '1 Summer Way' equal to the string '1 summer way'.  If you need to differentiate these values and are unable to change the collation at the server, database or column level, how can you differentiate these values?

Solution
One option is to specify the collation for the query to use a case sensitive configuration.  Let's show an example of a case sensitive search on a case insensitive SQL Server.

What is the collation?

As you can see, this SQL Server has a case insensitive collation i.e. CI.

T-SQL Command

SELECT SERVERPROPERTY ('Collation')
GO

Result Set

SQL_Latin1_General_CP1_CI_AS

What does the sample data look like?

The sample data has a mix of upper case, lower case and mixed case data as the 7 records show below.

T-SQL Command

SELECT *
FROM dbo.CaseSensitiveTest
GO

Result Set

UID Value1 DescValue1
1 TEST All Upper Case
2 test All Lower Case
3 Test Mixed Case
4 TEST1 All Upper Case
5 test1 All Lower Case
6 Test1 Mixed Case
7 123Test Mixed Case


How can I just capture the rows with the mixed case test values i.e. 'Test'?

Check out this query where a case sensitive collation is used:

T-SQL Command

SELECT *
FROM dbo.CaseSensitiveTest
WHERE Value1 LIKE '%Test%' Collate
SQL_Latin1_General_CP1_CS_AS
GO

Result Set

UID Value1 DescValue1
3 Test Mixed Case
6 Test1 Mixed Case
7 123Test Mixed Case

As you can see, only the 3 records where the 'Test' string is in mixed case are returned. 

Next Steps



Last Update: 8/3/2006


About the author
MSSQLTips author Jeremy Kadlec
Jeremy Kadlec is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com, Baltimore SSUG co-leader and SQL Server MVP since 2009.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Wednesday, September 17, 2014 - 4:55:02 PM - Michelle Read The Tip

This was just what I needed. We have a database that we generally want to be case insensitive, but if the case on names doesn’t match a comparison table from another system we need to do an update For instance if a name was entered as Mcpherson, then gets corrected to McPherson in the other system, we want to “see” that in our system and correct it there too. That will be the only time case matters.This was just what I needed. We have a database that we generally want to be case insensitive, but if the case on names doesn’t match a comparison table from another system we need to do an update For instance if a name was entered as Mcpherson, then gets corrected to McPherson in the other system, we want to “see” that in our system and correct it there too. That will be the only time case matters.

Thanks!

 

-- I did something like this as proof of concept. Gender is always
-- all caps in the database. The database is case insensitive If I use 
-- lower case, for my comparison value it will not return any results.

SELECT Gender
FROM MyDatabase
WHERE Gender Collate SQL_Latin1_General_CP1_CS_AS = 'm';


Wednesday, June 04, 2014 - 2:32:14 PM - David Read The Tip

You totally ROCK!!!


Thursday, May 09, 2013 - 1:52:04 PM - Kiran Read The Tip

Sorry Neema,

Your tip doesn't works in my case i.e., SQL_Latin1_General_CP1_CI_AS

It works works with Jeremy's tip i.e., SQL_Latin1_General_CP1_CS_AS

Neema I am not sure you are correct

 


Tuesday, May 07, 2013 - 3:29:44 AM - Irfan Kumte Read The Tip

 

 

 

Extremely helpful. Thanx.


Sunday, May 05, 2013 - 12:56:48 AM - Sumit Read The Tip

Great! It is working for me. Thank you very much.


Sunday, March 24, 2013 - 1:40:22 PM - Max Read The Tip

Thanks it helped allot 

thnku so much


Friday, January 11, 2013 - 6:13:27 PM - Jeremy Kadlec Read The Tip

Stortman,

Great!  I am happy to hear it!

Thank you,
Jeremy Kadlec
Community Co-Leader


Wednesday, January 09, 2013 - 6:45:21 PM - Stortman Read The Tip

Thanks for publishing this. I am developing using a desktop PC running SQL Server 2008 Management Studio and VWD 2010 Express under Win XP PRO SP3. The default installation is for case insensitive matching. I need case sensitive matching  in a stored procedure for valdating SQL Server user passwords stored in the database. This worked a treat. A localised fix.

SELECT @ImpId = ImpId

FROM dbo.Imp

WHERE ImpUserName = @ImpUserName

AND ImpPassword = @ImpPassword Collate SQL_Latin1_General_CP1_CS_AS

AND ...........

 

 


Tuesday, April 10, 2012 - 2:56:48 PM - Jeremy Kadlec Read The Tip

Tom,

I am happy to hear it!

Thank you,
Jeremy Kadlec


Tuesday, April 10, 2012 - 11:56:24 AM - Tom Holden Read The Tip

 

Of course that works like a charm.  Thank you.

Tom Holden


Friday, November 19, 2010 - 3:52:54 PM - Jeremy Kadlec Read The Tip

Vipul,

Good afternoon.  Can you please post your code (or a sample) so we have something to work with?

When you work through the examples in the tip, do they work for you?

What version of SQL Server are you using?

Thank you,
Jeremy Kadlec


Friday, November 19, 2010 - 6:16:00 AM - Vipul patel Read The Tip

when i use collate in my sql statement it's not working.. so tell me what's the problem in my sql server...


Friday, June 25, 2010 - 8:53:43 AM - nosnetrom Read The Tip
Another good one--and thanks to Neema for the correction/clarification!

Wednesday, May 28, 2008 - 4:07:05 PM - Neema Read The Tip

Thanks, this is a great tip!

I think you probably meant to use the collation _CS_ in the example you gave on the WHERE clause. You have a SQL statement that uses _CI.

Here's what I'm talking about: 

Check out this query where a case sensitive collation is used:

T-SQL Command

SELECT *
FROM dbo.CaseSensitiveTest
WHERE Value1 LIKE '%Test%' Collate
SQL_Latin1_General_CP1_CI_AS
GO

Result Set

UID Value1 DescValue1
3 Test Mixed Case
6 Test1 Mixed Case
7 123Test Mixed Case
 

As you can see, only the 3 records where the 'Test' string is in mixed case are returned.

Anyway, I did know what you were talking about so it is not a problem. Thanks once again for the tip.




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.