SQL SERVER – Collate – Case Sensitive SQL Query Search | SQL Server Journey with SQL Authority

« SQL SERVER – FIX : ERROR : Msg 3159, Level 16, State 1, Line 1 – Msg 3013, Level 16, State 1, Line 1

SQLAuthority.com 100th Post – Gratitude Note to Readers »

SQL SERVER – Collate – Case Sensitive SQL Query Search

April 30, 2007 by pinaldave

Case Sensitive SQL Query Search

If Column1 of Table1 has following values ‘CaseSearch, casesearch, CASESEARCH, CaSeSeArCh’, following statement will return you all the four records.

SELECT Column1 FROM Table1 WHERE Column1 = 'casesearch'

To make the query case sensitive and retrieve only one record (“casesearch”) from above query, the collation of the query needs to be changed as follows.
SELECT Column1 FROM Table1 WHERE Column1 COLLATE Latin1_General_CS_AS = 'casesearch'

Adding COLLATE Latin1_General_CS_AS makes the search case sensitive.

Default Collation of the SQL Server installation SQL_Latin1_General_CP1_CI_AS is not case sensitive.

To change the collation of the any column for any table permanently run following query.
ALTER TABLE Table1 ALTER COLUMN Column1 VARCHAR(20) COLLATE Latin1_General_CS_AS

To know the collation of the column for any table run following Stored Procedure.
EXEC sp_help DatabaseName

Second results set above script will return you collation of database DatabaseName.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

Posted in Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQL Utility, T SQL, Technology | Tagged SQL Collation | 104 Comments

104 Responses

on April 30, 2007 at 6:26 pm | Reply anonymous

This is good. I saved it.
- on February 1, 2012 at 4:39 pm | Reply aditya
  
  thanku pinal..
on June 9, 2007 at 1:40 am | Reply udhaya

it was very useful.
on June 14, 2007 at 1:03 pm | Reply Carlo

This helped resolve a case sensitivity issue with a DB application I was testing, a customer’s database collation was set to LATIN1_GENERAL_BIN which prevented our DB application from running it’s case variable statements thanks! :)
on July 5, 2007 at 12:29 am | Reply Julie

Hey,,

Really it’s good men………
Very Helpful too…..
on July 15, 2007 at 10:53 pm | Reply Ragesh

its very helpful

Thanks
Regards
Ragesh
on August 31, 2007 at 8:54 am | Reply Saeed Siddiqui

Dear Pinal
its not working, im using sql server 2005 and entered the below information:

SELECT Col2
FROM Table_1
WHERE Col2 COLLATE Latin1_General_CS_AS = ‘saeed’

but its give me err :

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘‘’.
- on November 7, 2009 at 11:15 am | Reply rakesh
  
  Hi,
  
  try like this
  
  SELECT Col2
  FROM Table_1
  WHERE Col2 COLLATE Latin1_General_CS_AS = ‘saeed’
- on March 26, 2010 at 8:58 pm | Reply Ashis
  
  Hi Saeed
  
  Try this
  
  SELECT Col2
  FROM Table_1
  WHERE Col2 COLLATE Latin1_General_CS_AS = ‘saeed’
- on May 12, 2010 at 4:10 pm | Reply Madhivanan
  
  It is becuase of the wrong representation of the single quote
  Copy the code and use single quote aound the value
on October 1, 2007 at 2:30 pm | Reply Kida

Thanks so much for the help… this was excactly what I needed.

Had I just found this page first instead… :)
on December 19, 2007 at 1:44 pm | Reply Anand K

Hi Dave,

Thank you very much for this help.This is the thing i needed.

Thank you once again
on February 6, 2008 at 12:29 pm | Reply Praveen

Thanks. Its working with SQLServer 2000
on February 19, 2008 at 4:22 pm | Reply ranjan

thanks brother

for your blogs
on March 19, 2008 at 7:28 am | Reply Justin J. Moses

v. handy.

thanx Pinal
on March 27, 2008 at 1:23 pm | Reply Nandan Mathure

Hi,

I am working on an application where end user will define his own collation. Can collation be specified on fly without using sp_executesql function.
- on May 12, 2010 at 4:15 pm | Reply Madhivanan
  
  No it is not possible you need to use dynamic sql anyway
on April 5, 2008 at 4:56 am | Reply Charlie

Brilliant! Thanks for finding this and getting out here where I could find it.

And did Saeed figure the problem was the wrong character for quoting text (‘saeed’ vs. ‘saeed’)?
on May 15, 2008 at 1:39 am | Reply Ahliana Byrd

You rock. THANKS. You solved what I was being told couldn’t really be solved. Excellent!
on May 23, 2008 at 7:13 pm | Reply HEMANT VERMA

very helpful….thank you very much.
on June 2, 2008 at 8:17 pm | Reply Deepak Joshi

Thanks, Pinal.
It is a very useful piece of information.
on June 5, 2008 at 3:17 pm | Reply ralpharama

Thanks for that useful page.
on June 17, 2008 at 1:23 pm | Reply MasterHoon

very useful. thanks!
on June 20, 2008 at 8:05 am | Reply shravan

it is to much helpful for me
on June 20, 2008 at 8:06 am | Reply shravan

Thanks
it is to much helpful for me
on August 8, 2008 at 4:40 pm | Reply Vallabh

EXEC sp_help DatabaseName … does not work

i replaced my database name above….it gave me error saying….
The object ‘aim0rodb’ does not exist in database ‘aim0rodb’.
where ‘aim0rodb’ is my database name…

any help……..

Thanks in advance…
- on May 12, 2010 at 4:21 pm | Reply Madhivanan
  
  EXEC sp_helpdb DatabaseName
on August 26, 2008 at 2:23 pm | Reply Koteswara Rao

You don’t need to give DatabaseName in EXEC sp_help if your object explorer already selected a database by default

You can give TableName in place of DatabaseName if data base is selected like EXEC sp_help
on September 4, 2008 at 11:23 am | Reply Poonam Gupta

it’s realy useful and helpful for me .thax to u
on September 8, 2008 at 4:16 pm | Reply Vipul

very useful & helpful as well!!
on September 8, 2008 at 11:04 pm | Reply kamn

Very Helpful.Thanks..You are Awesome !
on October 1, 2008 at 5:17 pm | Reply Sandy

Thanks resolved a problem I had with loading data from an Oracle db.
on December 1, 2008 at 11:10 pm | Reply Tina

I have updated a table’s length property using Alter table. However when running sp_help TableName it displays the old value for the length. How do I update this information?
- on May 12, 2010 at 4:22 pm | Reply Madhivanan
  
  That should not happen
  Are you sure you have queried the correct table name?
on January 7, 2009 at 4:12 pm | Reply Sanjeev

thanks
on February 14, 2009 at 10:21 am | Reply Sunil.P

Hey,

You are really genius…

Thanks man.
on February 26, 2009 at 5:32 pm | Reply SQL SERVER - 2008 - Interview Questions and Answers - Part 3 Journey to SQL Authority with Pinal Dave

[...] Width sensitivity – A single-byte character (half-width) and the same character represented as a double-byte character (full-width) are treated differently than it is width sensitive. (Read More Here) [...]
on June 4, 2009 at 5:42 pm | Reply Tamil

Its really helpful for me….. Thanks a lot
on July 17, 2009 at 3:23 pm | Reply Tess

loved it to the point no wasting anyone’s time thank you.
Its in my reference collection.
on September 17, 2009 at 5:08 pm | Reply Ashish Dev

Thanks
Your notes is Very usefull
on October 20, 2009 at 5:38 pm | Reply bhavin

hi,
thanks.
this helps me as well.
thanks.
on November 19, 2009 at 9:25 pm | Reply Ben

You da man! It worked.
on December 7, 2009 at 8:26 pm | Reply ruchi

Hi,
the material excellent on the website ……….appreciate ure help …..
I want to know
how to make an existing DB (that contains schema) case-insensitive ,is there a way to make the DB as well as the schema case-insensitive?
on December 8, 2009 at 3:38 am | Reply Pinal Dave

Hello Ruchi,

If the database is case sensitive then schema identfiers are case sensitive in t-sql code. For example if a database name TestDB has a table tblTEST then following statement runs successfully:

SELECT * FROM tblTEST

But below statement fails:

SELECT * FROM tbltest

You can change the collation of database by alter database statement as below:

ALTER DATABASE
COLLATE SQL_Latin1_General_CP1_CI_AS

After this the the schema identifiers in t-sql statements are case insensitive and above written both SELECT statements run successfully. But one more thing to consider here is that changing the collation of database does not change the collation of existing objects in that database. New collation will applied as default collation for all objects that are created after collate change.

Regards,
Pinal Dave
on December 22, 2009 at 10:36 pm | Reply Houman

I altered my table successfully, but when I select something from that table and group by the specific field, it doesn’t work. Even when I run sp_help it doesn’t show any collation.
- on May 12, 2010 at 4:30 pm | Reply Madhivanan
  
  EXEC sp_help ‘table_name’
  
  will show you table informations where one of the columns is collation
on January 12, 2010 at 5:44 pm | Reply Kapul

Really very helpful…….. Thanks
on January 27, 2010 at 6:25 pm | Reply Harish Goli

It helped me alot.. Thanks.
on February 9, 2010 at 12:55 pm | Reply jagadish

gud one.thanq really helping alot.
on February 19, 2010 at 1:49 pm | Reply Toothpick

Thanks so much for the help! I used the above advice and it made life so much easier!
on March 9, 2010 at 5:04 pm | Reply sandy

hi Pinal,

the stuff was useful.

Thanks & Regards
Sandesh Bhawke
on April 21, 2010 at 6:15 pm | Reply Rohit Gholap

Thanks a lotttttt………..u saved my atleast 4 hours.
on May 12, 2010 at 1:02 pm | Reply dazz

Thankz, Thankz a lot, this is very useful
on May 12, 2010 at 1:41 pm | Reply dazz

How to create a column as case sensitive while creating a new table?
- on May 12, 2010 at 4:06 pm | Reply Madhivanan
  
  Use CASE SENSITIVE collations like Latin1_General_CS_AS when defining the column
  
  ex
  
  declare @t table(c varchar(100) collate Latin1_General_CS_AS)
on May 27, 2010 at 9:12 pm | Reply Doug

Thank you. Time and again I have found your website and blogs very valuable and helpful. I appreciate your efforts.
on June 7, 2010 at 7:51 am | Reply Anil Sharma

I got cleared this concept very first time i am a beginer in sql.
Thax for ur kind suggestion.
on August 11, 2010 at 8:29 pm | Reply A_Graham

I tried using this example with a like and wildcards, but it still produced results of all data, both upper case and lower case:

SELECT PropertyState
FROM Property
WHERE PropertyState COLLATE Latin1_General_CS_AS like ‘%[a-z]%’

I just need to find all states with parts of the state text not all uppercase. (i.e. Ct, uT, ca, Id, hI, wa)
on August 11, 2010 at 8:44 pm | Reply A_Graham

I just found my answer:

…COLLATE Latin1_Genral_bin like ‘%[a-z]%’
on August 16, 2010 at 2:15 pm | Reply Edit

Hy,
Im one question
Wat is KS and WS (French_CI_AS_KS_WS) ?

Regards
Edit Paszka
on August 30, 2010 at 12:25 pm | Reply Manikandan.S

hi pinaldave,

thank you, this is very useful
on September 6, 2010 at 3:11 pm | Reply joby

It helped to resolve my problem.Thank you.
on October 16, 2010 at 5:05 pm | Reply Experts Comment Comments

Its in SQL and not LINQ
on October 16, 2010 at 5:12 pm | Reply RAMAN

GOOD ANSWER…..This code Help me
on November 17, 2010 at 5:35 pm | Reply ste

It made simple what others reported to be quite complicate to deal with (cross db query with different collations).
It helped me, thanks a lot,

Ste
on December 14, 2010 at 11:31 am | Reply Mayur

really useful always.keen to join community
on December 16, 2010 at 4:17 pm | Reply Antanas

Thank you very much for a help.
on December 22, 2010 at 12:34 pm | Reply Poongodi

Hi,

How to change the collation of database to Indic_General_90_CS_AS?

I tried out
sample is my database.
ALTER DATABASE sampleCOLLATE Indic_General_90_CS_AS

while executing this command i m getting error like

Collation ‘Indic_General_90_CS_AS’ is supported on Unicode data types only and cannot be set at the database or server level.

Can you help me!!!!!!

Regards,
Poongodi
on December 29, 2010 at 8:47 am | Reply Myat Thu

really useful, thank
on January 7, 2011 at 6:07 pm | Reply sanjay Patil

hi……
It is very useful.

Thanks
on February 18, 2011 at 4:08 pm | Reply priya

Hello Pinal and Everyone,
I am a teacher in London but would like to switch my career to sql programmer as I want to be database developer. I have done MCA and just recently passed my MCTS in sql2005. I am trying hard to get job as a sql programmar. I am applying here many jobs a day but they need experience.I always read your posts on the website.
can anybody help by giving me some voluntry work by that I can get experience.

I will very thankful to you.
waiting for your reply
on March 2, 2011 at 11:25 am | Reply ramakrishna Baru

Hi,

We have a similar situation recently and this is very useful. Will there be any impact on the performance if I use collate in where clause?
- on March 3, 2011 at 1:47 pm | Reply madhivanan
  
  Most likely index will not be used
on April 15, 2011 at 11:55 pm | Reply Prawin

Thanks a lot for the Collate Query. Shared with my colleagues.
on May 3, 2011 at 4:11 pm | Reply Md. Sumonur Rahman

Very nice thing I’ve ever found in my Programming Life…. Thanks Bro…

Md. Sumonur Rahman
Dot Net Programmer
Bangladesh
on May 26, 2011 at 3:25 pm | Reply ramakrishna

Thanks verymuch.. Its very useful
on June 2, 2011 at 10:09 am | Reply jyoti

thanks, its really helpful
on June 29, 2011 at 4:17 pm | Reply Rob Blij

You rock!
on July 6, 2011 at 7:01 am | Reply SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 6 of 31 Journey to SQLAuthority

[...] Width sensitivity – When a single-byte character (half-width) and the same character represented as a double-byte character (full-width) are treated differently, it is width sensitive. (Read more here) [...]
on July 7, 2011 at 4:16 pm | Reply gowsik

Hey, its really good ya…
on July 21, 2011 at 4:51 am | Reply bhu

Thank you, nice solution
on July 22, 2011 at 1:09 am | Reply Abdul

Thanks very neatly explained.
on August 4, 2011 at 6:10 pm | Reply pavan

thank you it helped me to clarify my doubt
on September 2, 2011 at 12:52 pm | Reply Pradeep

Really helpful. saved my time. Thanks.
on October 3, 2011 at 2:32 pm | Reply Ashish

HI ,I m facing a problem in SQL server plz guide me in that…

I m having a table in which a primary key is there with 2 columns(CODE nvarchar,VALUE nvarchar).This table contains the values in the Key columns as (X8900,A) but when I try to insert a new value as (X8900,a) ,its giving error message “primary key violation”.
Why its giving this error,if case is different for values column?
on October 18, 2011 at 4:17 pm | Reply Saadi

(Y) Good one….
on December 9, 2011 at 10:18 pm | Reply Murray Hibbard

Thank you so much ! ! ! ! ! !
on December 13, 2011 at 12:48 pm | Reply Yaroslav

Just great as usual. I used SQL_Latin1_General_Cp1253_CI_AI instead, so I do not care about capital letters or accents. Before I was using the typical replace instruction but performance began to be affected with big sets
on December 14, 2011 at 1:16 pm | Reply BABAR

really amazing sir …
awesome……
on January 24, 2012 at 7:32 pm | Reply mhbutt

This made my day. Thanks PD
on February 14, 2012 at 4:33 am | Reply Abachu

Hi,

How does it impact the performance by using collate clause in where conditions of the query?

How does this differs from using upper or lower functions on both sides of where clause conditions of the query?
on February 21, 2012 at 9:24 pm | Reply Bryon Jordan

Very helpful! Thanks!
on March 7, 2012 at 2:42 am | Reply sam p

thanks for the useful info!!!
on April 13, 2012 at 9:10 am | Reply Shruti

Really a good article
on April 20, 2012 at 2:21 pm | Reply Joe Samraj

Hi Sir,

SELECT Column1
FROM Table1
WHERE Column1 COLLATE Latin1_General_CS_AS LIKE ‘[A-Z]%’

th above query is not returning only the value statrs with capital letters why
on June 20, 2012 at 1:57 pm | Reply Vishal

great work
on June 27, 2012 at 3:40 pm | Reply vijay sharma

very good job sir,its working.vijay sharma
on September 11, 2012 at 5:18 pm | Reply lucky sai

while installing SQL2008R2 i have selected collation as case insensitive and now i would like to make case sensitive but HOW?
on September 20, 2012 at 2:09 am | Reply Don

This page helped me, but I had a different reason for needing it. The field in the table is already collated, which was preventing me from concatenating it with another string field.

This statement fails:

SELECT [ClassId]
,[Description]
,ClassId + ‘ ‘+ Description as Combo
FROM [inventory_class]

because ClassID is a collated field in the structure, but using the collate in the select statement allowed a concatenated combination of fields to be used as parameter list in a report:

This one works:

SELECT [ClassId]
,[Description]
,ClassId COLLATE Latin1_General_CS_AS + ‘ ‘+ Description as Combo
FROM [inventory_class]
on January 8, 2013 at 6:06 pm | Reply SVSaravanan

Thank you so much dear…
on April 3, 2013 at 7:01 am | Reply SQL SERVER – Resolve Cannot Resolve Collation Conflict Error – SQL in Sixty Seconds #047 | SQL Server Journey with SQL Authority

[...] Collate – Case Sensitive SQL Query Search [...]
on April 25, 2013 at 10:28 am | Reply ajay singh

Thanks and keep it up sir
It helped me
on May 4, 2013 at 7:00 am | Reply SQL SERVER – Weekly Series – Memory Lane – #027 | SQL Server Journey with SQL Authority

[...] Collate – Case Sensitive SQL Query Search Here is a quick script which does case sensitive SQL Query Search in the database. [...]
on May 13, 2013 at 8:11 pm | Reply doneel

This code helped me, thanks.
SQL_Latin1_General_CS_AS was invalid.
I used SQL_Latin1_General_CP1_CS_AS.