SQL Case Sensitive Search on a Case Insensitive SQL Server

Problem

Most SQL Server installations are installed with the default collation which is case insensitive.  This means that SQL Server ignores the case of the characters and treats the string ‘1 Summer Way’ equal to the string ‘1 summer way’.  If you need to differentiate these values and are unable to change the collation at the server, database or column level, how can you differentiate these values? Let’s look at how to do a SQL Case Sensitive Search.

Solution

One option is to specify the collation for the query to use a case sensitive configuration.  Let’s show an example of a case sensitive search on a case insensitive SQL Server.

What is the server collation?

We can check the collation for the server as follows:

SELECT SERVERPROPERTY ('Collation')
GO

What does this mean:

• SQL_Latin1_General_CP1 – English (United States)
• CI – case insensitive (ignores case)
• AS – accent sensitive (treats accented and unaccented characters differently)

What is the database collation?

The database could have a different collation then the SQL Server instance. This is not usually the case, but it is possible. This can be checked as follows.

SELECT DATABASEPROPERTYEX ('AdventureWorks','Collation')
GO

Create sample database and data

Below we create a database, add a table and insert some test records to test a case sensitive search.

-- Select database to create these objects
USE [YourDatabaseName]
GO
 
-- Create the table
CREATE TABLE [dbo].[CaseSensitiveTest] (
        [UID] [int] NOT NULL ,
        [Value1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [DescValue1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
 
-- Add primary key
ALTER TABLE [dbo].[CaseSensitiveTest] WITH NOCHECK ADD
        CONSTRAINT [PK_CaseSensitiveTest] PRIMARY KEY  CLUSTERED
        (
                [UID]
        )  ON [PRIMARY]
GO
 
-- Select server collation and sort information
SELECT SERVERPROPERTY('Collation'),
SERVERPROPERTY('SqlSortOrder')
GO
 
-- Insert test records
INSERT INTO CaseSensitiveTest VALUES (1, 'TEST', 'All Upper Case')
INSERT INTO CaseSensitiveTest VALUES (2, 'test', 'All Lower Case')
INSERT INTO CaseSensitiveTest VALUES (3, 'Test', 'Mixed Case')
INSERT INTO CaseSensitiveTest VALUES (4, 'TEST1', 'All Upper Case')
INSERT INTO CaseSensitiveTest VALUES (5, 'test1', 'All Lower Case')
INSERT INTO CaseSensitiveTest VALUES (6, 'Test1', 'Mixed Case')
INSERT INTO CaseSensitiveTest VALUES (7, '123Test', 'Mixed Case')

What does the sample data look like?

The sample data has a mix of upper case, lower case and mixed case data as the 7 records show below.

-- Select all records where value like Test
SELECT *
FROM dbo.CaseSensitiveTest
WHERE Value1 LIKE '%Test%'
GO

SQL Case Sensitive Search Example

Check out this query where a case sensitive collation is used:

-- Select all records where value like Test
SELECT *
FROM dbo.CaseSensitiveTest
WHERE Value1 LIKE '%Test%' COLLATE SQL_Latin1_General_Cp1_CS_AS
GO

As you can see, only the 3 records where the ‘Test’ string is in mixed case are returned.

Note: keep in mind that in order for SQL Server to search for these values it has to evaluate that column for all rows in the table because it has to change the value to case sensitive and then test the condition. This is true even if there is an index on that column and we change the WHERE clause to “WHERE Value1 LIKE ‘Test%’ COLLATE SQL_Latin1_General_Cp1_CS_AS”.

Next Steps

• To find out the collation for your SQL Server use the SERVERPROPERTY command with the ‘Collation’ property name.