I was asked today if it was possible to use a case-sensitive LIKE statement in a WHERE clause. The default SQL Server collation is case-insensitive, but the collation used can be specified in a query. To return the collation of the current database:
In my case, this returned Latin1_General_CI_AS. The case-sensitive version of this is Latin1_General_CS_AS. To use this collation for a column, specify it after the column using the COLLATE statement. For example, I have a table called Groups, with 2 rows. The first row has a GroupName of “Admin”, and the value in the second row is “Standard”. The following query returns both rows:
SELECT GroupID , GroupName FROM dbo.Groups WHERE GroupName LIKE '%a%'
GroupID GroupName 1 Admin 2 Standard
However, when we use the COLLATE statement to use the case-sensitive collation, only the second row is returned:
SELECT GroupID , GroupName FROM dbo.Groups WHERE GroupName COLLATE Latin1_General_CS_AS LIKE '%a%'
GroupID GroupName 2 Standard
Practical uses for this could include exact string searches and password matching.