Case Sensitive Compare

by
Facebooktwitterredditpinterestlinkedinmail

Occasionally we need to do a string comparison in SQL Server as a case sensitive compare.  By default, case sensitive compare is not turned on for tables in SQL Server.  It’s actually pretty easy to change a string comparison to a case sensitive compare.  You can do this by using the COLLATE clause.

Read below to find out why this works and other tips on doing a case sensitive compare.

SELECT	A.AnimalID, A.AnimalName, AFT.FeedingTime
FROM	Animal A
INNER	JOIN AnimalFeedingTime AFT ON AFT.AnimalName = A.AnimalName COLLATE SQL_Latin1_General_CP1_CS_AS

 
 
What Is The COLLATE Clause

The COLLATE clause is something that you can add to the end of an expression to set the collation of the operation to a specific collation.  So in the example… the string equal comparison is being changed to a collation of SQL_Latin1_General_CP1_CS_AS.

A collation in SQL Server is something defines how data is compared.  This could be things like case sensitivity, acceptable characters, etc…  Most of the time (for English installations of SQL Server) it will be a collation type of SQL_Latin1_General_CP1_CI_AS.  This is because the Latin character set covers English and other Latin based languages.

The two most common collations that I deal with are SQL_Latin1_General_CP1_CI_AS and SQL_Latin1_General_CP1_CS_AS.  You can see that they are very similar.  In fact… the only difference is the _CI_ and _CS_.  These actually stand for Case-Insensitive and Case-Sensitive.
 
 
Field Level Case Sensitivity

Even better… you don’t need to set the collation on every sql statement that you want to make case sensitive.  There is actually a setting that you can apply to a field in a table.

NOTE: that if you are joining on this table, both collations must be the same or you will need to specify the collation to use.

Create a new table with a case sensitive field

CREATE
TABLE	Animal
(AnimalID		INT,
AnimalName	VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS)

Change a table’s column to be case sensitive

ALTER
TABLE	Animals
ALTER	COLUMN AnimalName VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS