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:

SQL Server case sensitive string comparison

Another Example:

SELECT * 
   FROM MyTecBits_Table_1 
   WHERE [Name] COLLATE Latin1_General_CS_AI like '%My%'
GO
SQL Server case sensitive string comparison

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


Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.