Case Sensitive String Comparison on SQL Server

This article is about case sensitive string comparison using known string or characters. In my earlier article (Case Sensitive Search: Fetching lowercase or uppercase string on SQL Server), you have seen how to search for upper case or lower case or mixed case strings in case-sensitive way.

In this article I will show you how to compare a column with a known character or string in case-sensitive way. We can do the comparison using COLLATE.

Example for Case Sensitive String Comparison

To experiment the case sensitive string comparison, I’m using the same example table I’ve used in my earlier article. I want to compare one of the column against a known value (lower case ‘ec’) and list down all the records having lower case ‘ec’.

Below is the SQL statement which I can use for this type of case-sensitive string comparison.

SELECT * 
   FROM MyTecBits_Table_1 
   WHERE [Name] COLLATE Latin1_General_CS_AI like '%ec%'
GO

See the result of this statement:

Another Example:

SELECT * 
   FROM MyTecBits_Table_1 
   WHERE [Name] COLLATE Latin1_General_CS_AI like '%My%'
GO

To know more about SQL case-sensitive search, go to the article Case Sensitive Search: Fetching lowercase or uppercase string on SQL Server.