Interview Question of the Week #031 – How to do Case Sensitive SQL Query Search

Question – How to do case sensitive search on any column of the table?

Answer

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.

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

SQL Collation
Previous Post
SQL SERVER – Adding File to Database in AlwaysOn Availability Group
Next Post
SQL SERVER – Error: Msg 4305, Level 16, State 1 – The log in this backup set terminates at LSN, which is too early to apply to the database.

Related Posts

4 Comments. Leave new

Leave a Reply