pointers, solutions and scripts for the SQL DBA
not intended to replace msdn, common sense or oxford commas

12/16/2013

Case-Sensitive Updates in a Case-Insensitive World

Assuming you're in a case-insensitive ("CI" in the collation name) column, this UPDATE statement:

UPDATE tablefoo
set foo = 'Z'
where foo = 'z'

will just update all values where foo = 'z' or 'Z'. SQL Server is not by default case-sensitive, be it at the server, database, or column level. Each of those levels can have their own collation set differently, and there are rules for determining how they are enforced.

For example, in the US, this is the default collation: SQL_Latin1_General_CP1_CI_AS, a case-insensitive, accent-sensitive collation.

So, given this data:
tablename: tablefoo
foo
z
Z
Z
z
Z
z
How can we update just the three records where foo = 'z' but not the 3 records where foo = 'Z'? Without ALTER'ing any objects to change their collation, obviously...

UPDATE tablefoo
set foo = 'Z'
where foo COLLATE SQL_Latin1_General_CP1_CS_AS = 'z'

And then, to make our UPDATE statement non-repeatable for safety, we should add one more line:
UPDATE tablefoo
set foo = 'Z'
where foo COLLATE SQL_Latin1_General_CP1_CS_AS = 'z'
and foo COLLATE SQL_Latin1_General_CP1_CS_AS <> 'Z'
More reading on collations:
http://technet.microsoft.com/en-us/library/ms180175.aspx


2 comments:

  1. You could also try

    update tablefoo
    set foo = 'Z'
    where ascii(foo) = ascii('z')

    ReplyDelete

All comments are moderated before publishing. If you find something wrong or disagree, please comment so that this blog can be as accurate and helpful as possible.