By: Jeremy Kadlec | Comments (19) | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | > Query Optimization
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?
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 collation?
As you can see, this SQL Server has a case insensitive collation i.e. CI.
SELECT SERVERPROPERTY ('Collation')
GO
| Result Set |
|---|
| SQL_Latin1_General_CP1_CI_AS |
Create sample database and data
-- 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
| Result Set | ||
|---|---|---|
| UID | Value1 | DescValue1 |
| 1 | TEST | All Upper Case |
| 2 | test | All Lower Case |
| 3 | Test | Mixed Case |
| 4 | TEST1 | All Upper Case |
| 5 | test1 | All Lower Case |
| 6 | Test1 | Mixed Case |
| 7 | 123Test | Mixed Case |
How can I just capture the rows with the mixed case test values i.e. 'Test'?
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
| Result Set | ||
|---|---|---|
| UID | Value1 | DescValue1 |
| 3 | Test | Mixed Case |
| 6 | Test1 | Mixed Case |
| 7 | 123Test | Mixed Case |
As you can see, only the 3 records where the 'Test' string is in mixed case are returned.
Next Steps
- To find out the collation for your SQL Server use the SERVERPROPERTY command with the 'Collation' property name.
- For a listing of all of the SQL Server collations visit - SQL Collation Name.
- The next time you need to perform a case sensitive search with any string function check out this option and see if it will do the trick!
- Download the sample code here.
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
