Sep 30, 2012

How to perform case sensitive searches in SQL Server?

We all know that SQL server is case insesitive for the searches. Case Insensitive search means Sql server don’t differenciate between upper and lower case of words and would return as per spelling. But more often we have to make some case sensitive searches in the query. Here are few methods to perform this case sensitive search:

First Method and I think the most popular method is to cast and convert the field in varbinary.
For e.g.
Select Cust_Name from dbo.customers where cast(Cust_Id as varbinary(10)) = cast(@CustId as varbinary(10))
The above query will work perfect for the case sensitive search, but there would be performance loss because the field is used in function and hence instead of index seek query would scan the whole index to match the output.

Second Method is to attach the collate in query while searching through the queries.
For e.g.
Select Cust_Name from dbo.customers where Cust_Id = @CustId Collate SQL_Latin1_General_CP1_CS_AS
SQL_Latin1_General_CP1_CS_AS is collate which marks the column / query for case sensitive search. By default it's SQL_Latin1_General_CP1_CI_AS for each column. Again this query also scan the index table. Both the above query can work perfectly for small database where number of records are limited.

Third Method is to attach collate to column permanently to the table.
For e.g.
Alter table Customers
alter column Cust_Id varchar(10) collate SQL_Latin1_General_CP1_CS_AS

Select Cust_Name from dbo.customers where Cust_Id = @CustId

The above query will work perfectly for the case sensitive searches without any performance loss. This method is useful where we know about the field that will always have case sensitive search only.

All the above methods can be used to make case sensitive search in SQL Server.

Hope, it may be useful.


Blogs | About Me | My Projects | Contact Me