How do I do a simple 'Find and Replace" in MsSQL?

user_2908 0
Viewed 187059

Question is pretty self explanitory. I want to do a simple find and replace, like you would in a text editor on the data in a column of my database (which is MsSQL on MS Windows server 2003)

sql-server sql
5 Answers

The following query replace each and every a character with a b character.

UPDATE 
    YourTable
SET 
    Column1 = REPLACE(Column1,'a','b')
WHERE 
    Column1 LIKE '%a%'

This will not work on SQL server 2003.

user_7400

like so:

BEGIN TRANSACTION; 
UPDATE table_name
  SET column_name=REPLACE(column_name,'text_to_find','replace_with_this'); 
COMMIT TRANSACTION;

Example: Replaces <script... with <a ... to eliminate javascript vulnerabilities

BEGIN TRANSACTION; UPDATE testdb
SET title=REPLACE(title,'script','a'); COMMIT TRANSACTION;
user_29080

This pointed me in the right direction, but I have a DB that originated in MSSQL 2000 and is still using the ntext data type for the column I was replacing on. When you try to run REPLACE on that type you get this error:

Argument data type ntext is invalid for argument 1 of replace function.

The simplest fix, if your column data fits within nvarchar, is to cast the column during replace. Borrowing the code from the accepted answer:

UPDATE YourTable
SET Column1 = REPLACE(cast(Column1 as nvarchar(max)),'a','b')
WHERE Column1 LIKE '%a%'

This worked perfectly for me. Thanks to this forum post I found for the fix. Hopefully this helps someone else!

user_1083480

If you are working with SQL Server 2005 or later there is also a CLR library available at http://www.sqlsharp.com/ that provides .NET implementations of string and RegEx functions which, depending on your volume and type of data may be easier to use and in some cases the .NET string manipulation functions can be more efficient than T-SQL ones.

Joe_Kuemerle_42730
Related