In this blog post we are going to learn about how to do 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 the 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.
Here are few of the related blog posts on the same subject:
- SQL SERVER – Cannot resolve collation conflict for equal to operation
- SQL SERVER – Creating Database with Different Collation on Server
Let me know your feedback about this blog post in the comments section.
Reference: Pinal Dave (https://blog.sqlauthority.com)
120 Comments. Leave new
Thanks so much for the help! I used the above advice and it made life so much easier!
hi Pinal,
the stuff was useful.
Thanks & Regards
Sandesh Bhawke
Thanks a lotttttt………..u saved my atleast 4 hours.
Thankz, Thankz a lot, this is very useful
How to create a column as case sensitive while creating a new table?
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)
Thank you. Time and again I have found your website and blogs very valuable and helpful. I appreciate your efforts.
I got cleared this concept very first time i am a beginer in sql.
Thax for ur kind suggestion.
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)
I just found my answer:
…COLLATE Latin1_Genral_bin like ‘%[a-z]%’
Hy,
Im one question
Wat is KS and WS (French_CI_AS_KS_WS) ?
Regards
Edit Paszka
hi pinaldave,
thank you, this is very useful
It helped to resolve my problem.Thank you.
Its in SQL and not LINQ
GOOD ANSWER…..This code Help me
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
really useful always.keen to join community
Thank you very much for a help.
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
really useful, thank
hi……
It is very useful.
Thanks