Case-sensitive check/search/match in T-SQL - Sql Server 2005

   Tweet

Generally, SQL Server is not case-sensitive i.e SQL Server doesn't check,search or matching the values with case.

Though there are ways to make them case-sensitive using collation at various levels i.e at Server Level, Database Level, Column Level. This would make SQL Server case sensitive at that level permanantely.

In some case is required to matching value with case temporarily at particular T-SQL or in an If Condition. This case-sensitive check in SQL Server can be performed with following methods.

Method 1: Using COLLATE

Adding COLLATE Latin1_General_CS_AS makes the search case sensitive If condition matching can be done as

	IF('Case Sensitive Sql' COLLATE SQL_Latin1_General_CP1_CS_AS='CASE SENSITIVE SQL')
		PRINT 'Matched'
	ELSE
		PRINT 'Un-Matched'
	

Search can been done in T-SQL as

	SELECT mycolumn FROM mytable WHERE 
		 CAST(mycolumn AS VARBINARY(10)) = CAST('Case Sensitive Sql' AS VARBINARY(10)) 
	

Method 2: Using VARBINARY

Casting the given varchar value in equalent VARBINARY would determine the difference in value due to case.

	select CAST('Case Sensitive Sql' AS VARBINARY(100)) as Camelcase,CAST('CASE SENSITIVE SQL' AS VARBINARY(100)) as UpperCase
	

	IF(CAST('Case Sensitive Sql' AS VARBINARY(100))=CAST('CASE SENSITIVE SQL' AS VARBINARY(100)))
		PRINT 'Matched'
	ELSE
		PRINT 'Un-Matched'
	

Search can be done in T-SQL as

	SELECT mycolumn FROM mytable WHERE 
		 mycolumn COLLATE SQL_Latin1_General_CP1_CS_AS='Case Sensitive Sql'
	

   Tweet