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
This is good. I saved it.
thanku pinal..
it was very useful.
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! :)
Hey,,
Really it’s good men………
Very Helpful too…..
its very helpful
Thanks
Regards
Ragesh
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 ‘‘’.
Hi,
try like this
SELECT Col2
FROM Table_1
WHERE Col2 COLLATE Latin1_General_CS_AS = ‘saeed’
Hi Saeed
Try this
SELECT Col2
FROM Table_1
WHERE Col2 COLLATE Latin1_General_CS_AS = ‘saeed’
It is becuase of the wrong representation of the single quote
Copy the code and use single quote aound the value
Thanks so much for the help… this was excactly what I needed.
Had I just found this page first instead… :)
Hi Dave,
Thank you very much for this help.This is the thing i needed.
Thank you once again
Thanks. Its working with SQLServer 2000
thanks brother
for your blogs
v. handy.
thanx Pinal
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.
No it is not possible you need to use dynamic sql anyway
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’)?
You rock. THANKS. You solved what I was being told couldn’t really be solved. Excellent!
very helpful….thank you very much.
Thanks, Pinal.
It is a very useful piece of information.
Thanks for that useful page.
very useful. thanks!
it is to much helpful for me
Thanks
it is to much helpful for me