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)
4 Comments. Leave new
Good morning sir. Your friend from Pakistan. I regularly visit and read your blog.
Regards ZAHEER KHAN [Phone removed]
Sir is there any process by using which we can make our whole database case sensitive or insensitive ? Does CS and IS stands for case sensitive and case insensitive ?
Hi Manish,
As mentioned above sql server has default case insensitive collation. you can set collation as per your requirement at time of installation or you can alter it. see below links for ref.
https://blog.sqlauthority.com/2007/04/30/case-sensitive-sql-query-search/
https://docs.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-database-collation?view=sql-server-2017
hope this helps :)
I’m curious about something after reading this blog, and yes, I will try to set up a test later on….
First, if you need to use a collation in a WHERE clause of an indexed column, to get either the case-insensitive or case-sensitive results desired, are you guaranteeing a index scan versus a seek?
I’ve read where it is not good to have different collations for tempdb versus a database (or column in a database). I assume this is true. So, I’m wondering if one can create a computed column, with the desired (different) collation, add a similar index, and presumably regain the performance that might otherwise be lost.