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

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

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

This will not work on SQL server 2003.

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 testdbSET title=REPLACE(title,'script','a'); COMMIT TRANSACTION;

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 YourTableSET 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!