Question: How to Do Case Insensitive Search?
Answer: This is a very interesting question. Most of the time, I receive a question about how to do case sensitive search and I have already previously blogged that in the following blog posts:
However, during the recent Comprehensive Database Performance Health Check, I faced a unique situation where the database was case sensitive and our search was needed to be case insensitive. There are multiple ways to do the same but we finally decided to do it with the help of collation.
First, let us create a table with case sensitive column.
CREATE DATABASE MyDB
GO
CREATE TABLE CaseSensitive (Col1 VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS )
GO
INSERT INTO CaseSensitive (Col1)
VALUES ('ABC'), ('abc'), ('aBc')
GONext, write the following query and you will notice that it retrieves no results.
SELECT * FROM CaseSensitive WHERE Col1 = 'AbC' GO
This is because the string in the WHERE condition does not match the values in the table with regards to the case sensitivity.
Now to retrieve the data which is not dependent on the case, you can run the following statement with the collation in the WHERE condition.
SELECT * FROM CaseSensitive WHERE Col1 COLLATE SQL_Latin1_General_CP1_CI_AS = 'AbC' GO
Now when you run the above script you will notice that it will retrieve all the three values already inserted inside the table this is because with the help of collation we made the value case insensitive. Collation refers to a set of rules that determine how data is sorted and compared. Collations in SQL Server provide sorting rules, case, and accent sensitivity properties for your data. When you select a collation for your server, database, column, or expression, you’re assigning certain characteristics to your data.
Here is the official documentation where you can find all the supported collations in the SQL Server. Let me know if you use collation for your business and also use case in the comments area.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
