SQL Tips by Namwar Rizvi

May 14, 2007

Case Sensitive string comparison in SQL Server 2005

Filed under: Query,SQL Server 2005,string manipulation,tips,TSQL — namwar @ 8:35 PM

Normally most people install SQL Server with default collation which means SQL Server will compare strings by ignoring their case. If you want to perform a case sensitive query without changing the collation of your database environment then you need to specify the collation in your query. Following is the sample code to demonstrate how you can easily perform a case sensitive query in SQL Server 2005:

–Disables intermediate sql server messages
Set Nocount on

–Declare a test table for our example
Declare @m_TestTable table ( Id int, Name varchar(50) )

–Insert two sample records
Insert into @m_TestTable (id,Name) Values (1,‘London’)
Insert into @m_TestTable (id,Name) Values (2,‘london’)

–Perform a normal case insensitive search
Select * from @m_TestTable Where Name=‘London’

—Perform same search but with case sensitive search option
Select * from @m_TestTable Where Name=‘London’ COLLATE SQL_Latin1_General_Cp1_CS_AS

Advertisements

12 Comments »

  1. Really very Gr8 work

    Comment by hari — May 22, 2010 @ 7:14 AM | Reply

  2. This seems to work for me:
    Select * from @m_TestTable Where Name COLLATE SQL_Latin1_General_Cp1_CS_AS=‘London’

    Comment by jm — September 2, 2010 @ 11:49 AM | Reply

  3. Thanks it really works

    Comment by Saurabh — July 25, 2011 @ 1:12 PM | Reply

  4. Thanks ! It Works….

    Comment by Jitender — July 28, 2011 @ 10:56 AM | Reply

  5. Just what i was looking for, thanks.

    Comment by benjaminmumm — August 8, 2011 @ 2:07 PM | Reply

  6. Thank you. Works great.

    Comment by test97 — August 30, 2011 @ 5:21 PM | Reply

  7. Thanks for a wonderful solution..this really help me in solving a problem 🙂

    Comment by kailash — January 5, 2012 @ 7:19 AM | Reply

  8. NICE!! IT TAKES ME 2 HOURS TO LOOK FOR THE ANSWER, AND HERE IT IS!! THANK YOU!!

    Comment by Onjap — March 2, 2012 @ 4:59 AM | Reply

  9. Great…..osom…….
    you can say superb…..
    for a long time i am searching for that…. but finally got the result….
    thanks…a lot

    Comment by Ajay — May 9, 2012 @ 1:17 PM | Reply

  10. Great tip, thank you.

    Comment by anorwen — August 8, 2012 @ 10:31 PM | Reply

  11. Thanks for sharing, very useful

    Comment by steveculshaw — August 15, 2012 @ 12:38 PM | Reply

  12. Excellent…exactly what I was looking for. Concise and clear.
    Thanks for sharing Namwar.

    Comment by Qamer A. Shah (@qamerashah) — February 19, 2013 @ 10:01 PM | Reply


RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: