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.
- Cast the
TEXT
field to theNVARCHAR(max)
datatype using theCAST
function. - Perform your
REPLACE
on the output of #1. - 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.
- select cast(replace(cast(mytext as nvarchar(max)),'find','replace') as text)
- 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.]