Jan 21, 2014

How to perform case sensitive comparison in sql server

How to make or perform case sensitive search or comparison in WHERE clause in sql server

Sql sever default comparison is case insensitive. For example suppose we have Customer table

CustomerID
CompanyName
Country
1
Microsoft
usa
2
IBM
USA
  
SELECT CustomerID,CompanyName
FROM Customer
WHERE Country = 'USA'

Output: 

CustomerID
CompanyName
1
Microsoft
2
IBM

We can observe comparison is case insensitive. It is due to default collation SQL_Latin1_General_CP1_CI_AS which is case insensitive.   We can make it case sensitive by using case sensitive collation. For example:

SELECT CustomerID,CompanyName
FROM Customer
WHERE Country = 'USA' COLLATE SQL_Latin1_General_CP1_CS_AS

Output:

CustomerID
CompanyName
2
IBM

No comments:

Post a Comment