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.

SQL SERVER - Case-Sensitive Search case-sensitive-800x429

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.

FROM Names
WHERE NamesCol = 'jack'

SQL SERVER - Case-Sensitive Search jack3

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.

FROM Names
WHERE NamesCol COLLATE Latin1_General_CS_AS = 'jack'

SQL SERVER - Case-Sensitive Search jack1

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)

SQL Collation, SQL Scripts, SQL Search, SQL Server, SQL String
Previous Post
SQL SERVER – Top 3 Wait Stats from Real-World
Next Post
SQL SERVER – Maximum Column Per Index – 32

Related Posts

Leave a Reply