Performing a case insensitive search in SQL Server can be a common requirement in many scenarios. In this article, we will explore how to achieve this using collation.

Let’s start by creating a table with a 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

Now, if we try to perform a case sensitive search using the following query, we won’t get any 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.

To retrieve the data without considering the case, we can use collation in the WHERE condition:

SELECT *
FROM CaseSensitive
WHERE Col1 COLLATE SQL_Latin1_General_CP1_CI_AS = 'AbC'
GO

By specifying the collation SQL_Latin1_General_CP1_CI_AS, we make the search case insensitive. Running the above script will retrieve all the three values already inserted into the table.

Collation refers to a set of rules that determine how data is sorted and compared. In SQL Server, collations provide sorting rules, case sensitivity, and accent sensitivity properties for your data. When you select a collation for your server, database, column, or expression, you are assigning certain characteristics to your data.

You can find all the supported collations in SQL Server in the official documentation.

Using collation is a powerful technique to perform case insensitive searches in SQL Server. If you have any experience or use cases related to collation, feel free to share them in the comments section below.