How to Do Case Insensitive Search? – Interview Question of the Week #267

Question: How to Do Case Insensitive Search?

How to Do Case Insensitive Search? - Interview Question of the Week #267 Case-Insensitive-800x280

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.

Solarwinds

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')
GO

Next, 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)

Solarwinds
, , ,
Previous Post
How to Join Two Tables Without Using Join Keywords? – Interview Question of the Week #266
Next Post
How to Decode @@OPTIONS Value? – Interview Question of the Week #268

Related Posts

Leave a Reply

Menu