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 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('If the result is 0 you are in a case-sensitive collation mode', 'RESULT')

A result of 0 indicates you are in a case-sensitive collation mode, 1 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'.

Answer 3

Hi,

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.

Answer 4

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.

Answer 5

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'

Answer 6

Collation is used to perform case sensitive search with SQL. Below link provide details description of case sensitive operation with SQL

http://www.a2zmenu.com/MySql/Case-sensitive-search-in-SQL.aspx