Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Case and Accent Sensitivity in SQL Server

RSS
Modified on Tue, Jun 04, 2013, 4:24 PM by Administrator Categorized as SQL Server
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

With Unicode String Literals

When specifying a Unicode string literal, prefix the first single-quote with an N.

@@select *
from dbo.Restaurants
where Name = N'Café'

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2017, Patrick Jasinski.