Post
Ask Question

Case Sensitive Search in SQL Server

Today I worked on searching tool. I faced a problem to search case sensitive.When I used like or = operator then we can’t handle case sensitive. I have three common method for case sensitive search in SQL Server.

  1. Converting data to binary type before comparison.
  2. Using the COLLATE clause to dictate the case sensitiveness of the query.
  3. Using BINARY_CHECKSUM function.
We create a table and insert some data.
  1. CREATE TABLE #TEMP(ID INT IDENTITY (1,1),NAME VARCHAR (10))  
  2.   
  3. INSERT INTO #TEMP(NAME) VALUES ('aMIT')  
  4.   
  5. INSERT INTO #TEMP(NAME) VALUES ('amIT')  
  6. INSERT INTO #TEMP(NAME) VALUES ('AMIT')  
  7. INSERT INTO #TEMP(NAME) VALUES ('AMIT')  
  8. INSERT INTO #TEMP(NAME) VALUES ('aMiT')  
When we use normal select statement then it display all data.
  1. SELECT * FROM #TEMP WHERE NAME ='AMIT'  

Converting data to binary type before comparison

COLLATE clause allow to specify a particular collection for example we use COLLATE clause to make our search case sensitive for search case Sensitive we used “SQL_Latin1_General_CP1_CS_AS”

  1. SELECT * FROM #TEMP WHERE NAME COLLATE Latin1_General_CS_AS ='AMIT' 

Converting data to binary type before comparison

In this method when we convert character in binary or binary data type that character get ASCII value and you know each character has its own value. So we can easily match value:

  1. SELECT * FROM #TEMP  WHERE CAST (NAME AS VARBINARY(20))=CAST('AMIT' AS VARBINARY(20))   

Using BINARY_CHECKSUM function

BINARY_CHECKSUM () function accepts input, and returns a checksum value for that input and with the help of this function we match string.

For example

  1. SELECT * FROM #TEMP  WHERE BINARY_CHECKSUM(NAME)=BINARY_CHECKSUM('AMIT')  
All Query given Following out put.