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
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
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?
Most likely index will not be used
Thanks a lot for the Collate Query. Shared with my colleagues.
Very nice thing I’ve ever found in my Programming Life…. Thanks Bro…
Md. Sumonur Rahman
Dot Net Programmer
Bangladesh
Thanks verymuch.. Its very useful
thanks, its really helpful
You rock!
Hey, its really good ya…
Thank you, nice solution
Thanks very neatly explained.
thank you it helped me to clarify my doubt
Really helpful. saved my time. Thanks.
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?
(Y) Good one….
Thank you so much ! ! ! ! ! !
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
really amazing sir …
awesome……
This made my day. Thanks PD
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?
Very helpful! Thanks!