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
AnimalName VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS)
Change a table’s column to be case sensitive
ALTER COLUMN AnimalName VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS