SQL SERVER – Case-Sensitive Search

How to do a Case-sensitive Search? I was asked this by one of my clients of Comprehensive Database Performance Health Check. Well, I have blogged about this before here and here. However, let us understand the same with the help of an example.

Let us first create a table.

CREATE TABLE Names (NamesCol VARCHAR(100));

Now let us populate it with few strings which all contain similar letters.

INSERT INTO Names (NamesCol)
VALUES ('Jack'),('jack'),('jacK');

Now let us retrieve the data from this table and you will notice that the result returns all the rows.

SELECT *
FROM Names
WHERE NamesCol = 'jack'

However, if you want to retrieve only the rows which match the case of the string jack, you will have to do the case-sensitive search. The reason the query return all the rows is because the database collation is case insensitive. Now let us run the following script where I have specified case-sensitive collation for the WHERE clause.

SELECT *
FROM Names
WHERE NamesCol COLLATE Latin1_General_CS_AS = 'jack'

When you run the query above, it will return a single result matching the string in the WHERE clause.

Well, that’s it. The collation of the database is very important. You can run the following command to list the collation of all the available databases on your system.

SELECT name, collation_name
FROM sys.databases

If you have any questions, please do not hesitate to reach out to me. If you want me to create a video on this topic on SQL in Sixty Seconds Video, do let me know and I will create one. You can always reach out to me on Twitter.

Reference: Pinal Dave (https://blog.sqlauthority.com)