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 - Current Revision

Page Revision: Tue, Jun 04, 2013, 3:56 PM

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

select databaseproperty('MyDatabase', 'collation') as result

This statement will yield a single-row result set, containing something like 'SQL_Latin1_General_CP1_CI_AS'. (Note that if the database collation is NULL, it reverts to the server collation.)

• 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