Interview Question of the Week #045 – How to Do Case Sensitive Search?

One of my popular questions which helps me to understand lots of understanding of SQL by SQL developer.

Question: How doing a case sensitive search in SQL Server?

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

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

Quest

Previous Post
SQL SERVER – Installation Failed With Error – Wait on the Database Engine Recovery Handle Failed
Next Post
MySQL – Get Latest Identity Value by Inserts

Related Posts

No results found.

2 Comments. Leave new

  • Using COLLATE Latin1_General_CS_AS from last couple of months in one of our legacy application since collation setting was configured at DB level, and now after many request we finally got rid of this case sensitive setting in our new application.

    Still I am clueless, under what circumstances we must have enforced this setting at DB level, It was very strange when we saw, we can have 2 columns in a table with same name.

    Just an example :
    Select Salary,salary from Employee

    Also we can have 2 tables with same name in a DB
    Just an example :
    Select top 1 * from Employee
    Select top 1 * from employee

    Reply
  • Using COLLATE Latin1_General_CS_AS from last couple of months in one of our legacy application since collation setting was configured at DB level, and now after many request we finally got rid of this case sensitive setting in our new application.

    Still I am clueless, under what circumstances we must have enforced this setting at DB level, It was very strange when we saw, we can have 2 columns in a table with same name.

    Just an example :
    Select Salary,salary from Employee

    Also we can have 2 tables with same name in a DB
    Just an example :
    Select top 1 * from Employee
    Select top 1 * from employee

    Reply

Leave a Reply