How to run a case-sensitive search in SQL Server using Collations
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.
SELECT DISTINCT [status]FROM (SELECT 'Active' as statusUNIONSELECT 'Active' as statusUNIONSELECT 'ACTIVE' as statusUNIONSELECT 'Active' as status) temp/* OUTPUT:status------Active*/
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
SELECT *FROM (SELECT 1 as id, 'Active' as statusUNIONSELECT 2 as id, 'Active' as statusUNIONSELECT 3 as id, 'ACTIVE' as statusUNIONSELECT 4 as id, 'Active' as status) tempWHERE [status] = 'ACTIVE';/* OUTPUT:id status----------- ------1 Active2 Active3 ACTIVE4 Active*/
Enter, SQL Server Collations
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:
SELECT *FROM (SELECT 1 as id, 'Active' as statusUNIONSELECT 2 as id, 'Active' as statusUNIONSELECT 3 as id, 'ACTIVE' as statusUNIONSELECT 4 as id, 'Active' as status) tempWHERE [status] = 'ACTIVE' COLLATE SQL_Latin1_General_CP1_CS_AS;/* OUTPUT:id status----------- ------3 ACTIVE*/
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.
-- Server-Level CollationSELECT SERVERPROPERTY('collation') AS 'Server Collation';-- Database-Level CollationSELECT DATABASEPROPERTYEX('<your-database-name>','Collation') AS 'Database Collation';-- Column-Level CollationSELECTname as 'Column',collation_name as 'Column Collation'FROM sys.columnsWHERE name = N'<your-column-name>';