SQL SERVER – Collate – Case Sensitive SQL Query Search

In this blog post we are going to learn about how to do Case Sensitive SQL Query Search.

SQL SERVER - Collate - Case Sensitive SQL Query Search casesensitive

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:

Let me know your feedback about this blog post in the comments section.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , ,
Previous Post
SQL SERVER – FIX : ERROR : Msg 3159, Level 16, State 1, Line 1 – Msg 3013, Level 16, State 1, Line 1
Next Post
SQLAuthority.com 100th Post – Gratitude Note to Readers

Related Posts

120 Comments. Leave new

  • thanks for the useful info!!!

    Reply
  • Really a good article

    Reply
  • 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

    Reply
  • great work

    Reply
  • vijay sharma
    June 27, 2012 3:40 pm

    very good job sir,its working.vijay sharma

    Reply
  • while installing SQL2008R2 i have selected collation as case insensitive and now i would like to make case sensitive but HOW?

    Reply
  • 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]

    Reply
  • Thank you so much dear…

    Reply
  • Thanks and keep it up sir
    It helped me

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

    Reply
  • very useful

    Reply
  • 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.

    Reply
  • thanks a lot…………….

    Reply
  • nice article

    Reply
  • 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.

    Reply
  • Thank you so much, your article helped me a lot! Greetings from Panama

    Reply
  • Collation can be both side like database and server or not?

    Reply
  • 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

    Reply
  • This is really helpful … Thanks!!

    Reply
  • Thank you very much for this help. This is the thing I needed.
    Vinay

    Reply

Leave a Reply

Menu