HOW TO USE REPLACE() WITHIN TEXT COLUMNS IN SQL SERVER

SQL has an incredibly useful function, REPLACE(), which replaces all occurrences of a specified string with another string, returning a new string. It works great with all forms of NCHAR and NVARCHAR fields. It does not, however, work with NTEXT and TEXT  fields.

Fear not — there’s an easy workaround, thanks to type-casting and SQL 2005’s NVARCHAR(max) datatype. Here’s the process in an nutshell.

1. Cast the TEXT field to the NVARCHAR(max) datatype using the CAST function.
2. Perform your REPLACE on the output of #1.
3. Cast the output of #2 back to TEXT. (Not really required, but it does get us back to where we started.

A simple SQL query illustrates this.

1. select cast(replace(cast(mytext as nvarchar(max)),'find','replace') as text)  
2. from mytexttable  

If you’re using SQL 2000, you’re out of luck, as NVARCHAR(max) first appeared in SQL 2005. However, if your TEXT field is less than 8000 characters, you can cast it to VARCHAR(8000) — the largest possible VARCHAR size — to accomplish the same.

[Note #1: This solution below will also work with TEXT fields. Simply replace TEXT with NTEXT , and NVARCHAR withVARCHAR.]

[Note #2: NTEXT fields are depreciated in SQL 2005 in favor of NVARCHAR(max), so avoid using TEXT and you'll avoid this problem altogether in the future.]