Page History: Case and Accent Sensitivity in SQL Server

Compare Page Revisions

2 1 0 Current 2 1 0

« Older Revision - Back to Page History - Newer Revision »

Page Revision: Wed, Feb 20, 2013, 11:45 AM

Most instances of SQL server have been installed with the default collation, which is case-insensitive. This means that when comparing strings in a WHERE class, the case of letters is NOT considered. If case is important, you can use the following strategy.

Current Collation¶

select serverproperty('collation') as result

This statement will yield a single-row result set, containing something like 'SQL_Latin1_General_CP1_CI_AS'.

• The CI means "case-insensitive"; CS would mean "case-sensitive".
• The AS means "accent-sensitive"; AI would mean "accent-insensitive".
  

Forcing Case-Sensitivity¶

select *
from dbo.Documents
where Contents collate SQL_Latin1_General_CP1_CS_AS like '%Brown%'

Ignoring Accents¶

This is also known as "accent-insensitivity".

select *
from dbo.Restaurants
where Name collate SQL_Latin1_General_CP1_CI_AI like 'Cafe'

This statement will return Restaurants rows where the name contains 'Cafe' or 'Café'.

With the REPLACE Function¶

select result = REPLACE('This is an Accurate test' COLLATE SQL_Latin1_General_CP1_CS_AS, 'A','*')

Yields

This is an *ccurate test