How do I perform a case-sensitive search and replace in SQL 2000/2005?

Question

In order to perform a case-sensitive search/replace on a table in a SQL Server 2000/2005 database, you must use the correct collation.

How do you determine whether the default collation for a database is case-sensitive, and if it isn't, how to perform a case-sensitive search/replace?

Answer 1

SELECT testColumn FROM testTable  
    WHERE testColumn COLLATE Latin1_General_CS_AS = 'example' 

SELECT testColumn FROM testTable
    WHERE testColumn COLLATE Latin1_General_CS_AS = 'EXAMPLE' 

SELECT testColumn FROM testTable 
    WHERE testColumn COLLATE Latin1_General_CS_AS = 'eXaMpLe' 

Don't assume the default collation will be case sensitive, just specify a case sensitive one every time (using the correct one for your language of course)

Answer 2

Determine whether the default collation is case-sensitive like this:

select charindex('RESULT', 'If the result is 0 you are in a case-sensitive collation mode')

A result of 0 indicates you are in a case-sensitive collation mode, 8 indicates it is case-insensitive.

If the collation is case-insensitive, you need to explicitly declare the collation mode you want to use when performing a search/replace.

Here's how to construct an UPDATE statement to perform a case-sensitive search/replace by specifying the collation mode to use:

update ContentTable
set ContentValue = replace(ContentValue COLLATE Latin1_General_BIN, 'THECONTENT', 'TheContent')
from StringResource
where charindex('THECONTENT', ContentValue COLLATE Latin1_General_BIN) > 0

This will match and replace 'THECONTENT', but not 'TheContent' or 'thecontent'.