So the other day I was tasked with running a search in SQL Server to find all rows in a particular entity table with a particular column called
status set to ACTIVE in all caps.
Most of the rows in this table had the value as
Active for the
status column but I was not sure whether it also had value as
So first I ran a query to find all distinct status values.
1SELECT DISTINCT [status]2FROM (3 SELECT 'Active' as status4 UNION5 SELECT 'Active' as status6 UNION7 SELECT 'ACTIVE' as status8 UNION9 SELECT 'Active' as status10) temp1112/* OUTPUT:1314status15------16Active1718*/
Did you see what happened? The all caps
ACTIVE value is just ignored and we only get the other value in the result set.
This is because most SQL Server installations are case-insensitive.
So if you run a query like this to return only those rows with
status set to
ACTIVE in all caps, it won't work. It'll return rows with
status column value set to
1SELECT *2FROM (3 SELECT 1 as id, 'Active' as status4 UNION5 SELECT 2 as id, 'Active' as status6 UNION7 SELECT 3 as id, 'ACTIVE' as status8 UNION9 SELECT 4 as id, 'Active' as status10) temp11WHERE [status] = 'ACTIVE';1213/* OUTPUT:1415id status16----------- ------171 Active182 Active193 ACTIVE204 Active2122*/
Collations are a set of rules in SQL Server for storing and fetching data. These rules are defined on a server, database or column level and can even be specified in queries.
The two main collations we need to know at this point are:
SQL_Latin1_General_CP1_CI_ASwhere CI stands for case-insensitive
SQL_Latin1_General_CP1_CS_ASwhere CS stands for case-sensitive
So how can we use this to make our search case-sensitive? The answer is using the
We can make our search queries run case-sensitively by using the
COLLATE clause followed by the name of the case-sensitive collation like this:
1SELECT *2FROM (3 SELECT 1 as id, 'Active' as status4 UNION5 SELECT 2 as id, 'Active' as status6 UNION7 SELECT 3 as id, 'ACTIVE' as status8 UNION9 SELECT 4 as id, 'Active' as status10) temp11WHERE [status] = 'ACTIVE' COLLATE SQL_Latin1_General_CP1_CS_AS;1213/* OUTPUT:1415id status16----------- ------173 ACTIVE1819*/
Woo hoo!🤘 Mission accomplished! We now get only those rows that have
status column value as all caps
We can also find out the collation specified for servers, databases and columns using these helpful T-SQL queries.
1-- Server-Level Collation2SELECT SERVERPROPERTY('collation') AS 'Server Collation';34-- Database-Level Collation5SELECT DATABASEPROPERTYEX(6 '<your-database-name>',7 'Collation'8) AS 'Database Collation';910-- Column-Level Collation11SELECT12 name as 'Column',13 collation_name as 'Column Collation'14FROM sys.columns15WHERE name = N'<your-column-name>';
Hi!👋, I'm Saurabh Misra, a full-stack software developer.