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

sql sql-server-2005 sql-server-2000 collation
Andrew Myhre picture Andrew Myhre · Sep 22, 2008 · Viewed 20k times · Source

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

blowdart picture blowdart · Sep 22, 2008 
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)

Related questions

Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Schema, Owner for objects in MS SQL

By default, objects (tables, stored procedures, etc) are set up with the dbo owner/schema (I think ms sql 2000 calls it owner, while ms sql 2005 calls it schema) The owner/schema is really a role or user in the database. …

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More