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?

Moribund

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)

Aeschines

Answer 2

If you have different cases of the same word in the same field, and only want to replace specific cases, then you can use collation in your REPLACE function:

UPDATE tableName
SET fieldName = 
    REPLACE(
        REPLACE(
            fieldName COLLATE Latin1_General_CS_AS,
            'camelCase' COLLATE Latin1_General_CS_AS,
            'changedWord'
        ),
        'CamelCase' COLLATE Latin1_General_CS_AS,
        'ChangedWord'
    )

This will result in:

This is camelCase 1 and this is CamelCase 2

becoming:

This is changedWord 1 and this is ChangedWord 2

Transfinite

Answer 3

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'.

Moribund

Answer 4

Can be done in multiple statements. This will not work if you have long strings that contain both capitalized an lowercase words you intend to replace. You might also need to use different collation this is accent and case sensitive.

UPDATE T SET [String] = ReplacedString
FROM [dbo].[TranslationText] T, 
    (SELECT [LanguageCode]
      ,[StringNo]
      ,REPLACE([String], 'Favourite','Favorite') ReplacedString
    FROM [dbo].[TranslationText]
    WHERE 
    [String] COLLATE Latin1_General_CS_AS like '%Favourite%'
    AND [LanguageCode] = 'en-us') US_STRINGS
WHERE 
T.[LanguageCode] = US_STRINGS.[LanguageCode] 
AND T.[StringNo] = US_STRINGS.[StringNo]

UPDATE T SET [String] = ReplacedString
FROM [dbo].[TranslationText] T, 
    (SELECT [LanguageCode]
      ,[StringNo]
      , REPLACE([String], 'favourite','favorite') ReplacedString 
    FROM [dbo].[TranslationText]
    WHERE 
    [String] COLLATE Latin1_General_CS_AS like '%favourite%'
    AND [LanguageCode] = 'en-us') US_STRINGS
WHERE 
T.[LanguageCode] = US_STRINGS.[LanguageCode] 
AND T.[StringNo] = US_STRINGS.[StringNo]

Discovery

Answer 5

First of all check this: http://technet.microsoft.com/en-us/library/ms180175(SQL.90).aspx

You will see that CI specifies case-insensitive and CS specifies case-sensitive.

Jabez

Answer 6

Also, this might be usefull. select * from fn_helpcollations() - this gets all the collations your server supports. select * from sys.databases - here there is a column that specifies what collation has every database on your server.

Jabez

Answer 7

You can either specify the collation every time you query the table or you can apply the collation to the column(s) permanently by altering the table.

If you do choose to do the query method its beneficial to include the case insensitive search arguments as well. You will see that SQL will choose a more efficient exec plan if you include them. For example:

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

Correlate