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 for the useful info!!!
Really a good article
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
great work
very good job sir,its working.vijay sharma
while installing SQL2008R2 i have selected collation as case insensitive and now i would like to make case sensitive but HOW?
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]
Thank you so much dear…
Thanks and keep it up sir
It helped me
This code helped me, thanks.
SQL_Latin1_General_CS_AS was invalid.
I used SQL_Latin1_General_CP1_CS_AS.
very useful
Hi,
below query returns result in both the cases. like query ignores {ȧ} characters
Actual Data
ncharTitle
———–
JȧSØÑ
when running query
select * from sampletable where ncharTitle like (N’%JȧSØÑ%’)
select * from sampletable where ncharTitle like (N’%JSØÑ%’)
returns same result ignoring ‘ȧ’ in 2nd query.
My collation is Latin1_General_CI_AI
Any help will be highly appreciated.
thanks a lot…………….
nice article
i want to add French word like Catégories in my table with EncryptByKey. Here is my query:
OPEN SYMMETRIC KEY SymmetricKey1 DECRYPTION BY CERTIFICATE Certificate1; insert into tbl_Name (Name) values (EncryptByKey( Key_GUID(‘SymmetricKey1′), CONVERT(Nvarchar,’Catégories’)))
but when i retrieve the value with following query, i got 慃㽴潧楲獥 instead of Catégories
OPEN SYMMETRIC KEY SymmetricKey1 DECRYPTION BY CERTIFICATE Certificate1; SELECt CONVERT(NVARCHAR(max),DECRYPTBYKEY(Name)) as Name from tbl_Name
Expression type varbinary(max) is invalid for COLLATE clause.
Thank you so much, your article helped me a lot! Greetings from Panama
Collation can be both side like database and server or not?
I have name ‘MATT MCANDREW’ in 3 rows under a column in table, when I do a select it it returning 3 rows for that name in upper case, but when I do a DISTINCT column for that name, it is returning the data in proper case(Matt McAndrew). Database and table collation is COLLATE Latin1_General_CI_AS, and if I add COLLATE Case sensitive in distinct statement, it is returning name in uppercase. Could someone please let me know the reason and fix for this, it is happening only for this name alone, and working fine for other names. I really appreciate your help on this. Thanks
SELECT ARTIST FROM L_PRODUCT
WHERE ARTIST IN (‘MATT MCANDREW’)
MATT MCANDREW MATT MCANDREW MATT MCANDREW
SELECT DISTINCT ARTIST FROM L_PRODUCT
WHERE ARTIST IN (‘MATT MCANDREW’)
Matt McAndrew
SELECT DISTINCT ARTIST COLLATE Latin1_General_CS_AS FROM L_PRODUCT
WHERE ARTIST IN (‘MATT MCANDREW’)
MATT MCANDREW
This is really helpful … Thanks!!
Sure, thanks for your note.
Thank you very much for this help. This is the thing I needed.
Vinay
Great. VInay, I am glad that this blog could provide you help.