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

  • hi, I have a problem in executing sql qeury, i need get records from the table, but currently while executing query “Select distinct(*) from [EwpIam$]” , it does not consider case sensitive. Example Kliang and kliang are unique records, but this considers them as one. Any advise?

    Reply
  • Sumeesh Mukundan
    May 24, 2016 6:31 pm

    Hi,
    I have a script which is scheduled to run every hour is running successful on a database which is case in sensitive.
    but the same is failing randomly, like once in a week or 2 weeks or so.
    what could be the reason? and why it fails randomly.

    Reply
  • Thank you Sir,

    Reply
  • Should youbput that in your where clause will the Index still be kept in mind or ignored?

    Reply
  • G Prem Kumar
    July 12, 2018 4:08 pm

    Hi,

    I have a query in regards to search script in this link “http://thesitedoctor.co.uk/blog/how-to-search-every-table-and-field-in-a-sql-server-database/” how to make this one as a case sensitive search script. Because if the database is case sensitive by initiating the above search script it is not picking the results as expected, hence need to update the above one as case sensitive so that whatever value we search for should pick the results accordingly….

    Reply
  • Can you help to differentiate between œ and oe … didnt help using Latin1_General_CS_AS collate..

    Reply

Leave a Reply

Menu