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
NVARCHAR fields. It does not, however, work with
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
NTEXTfield to the
NVARCHAR(max)datatype using the
- Perform your
REPLACEon the output of #1.
- Cast the output of #2 back to
NTEXT. (Not really required, but it does get us back to where we started.
A simple SQL query illustrates this.
select cast(replace(cast(myntext as nvarchar(max)),'find','replace') as ntext) from myntexttable
If you’re using SQL 2000, you’re out of luck, as
NVARCHAR(max) first appeared in SQL 2005. However, if your
NTEXT 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
NTEXT fields are depreciated in SQL 2005 in favor of
NVARCHAR(max), so avoid using
NTEXT and you'll avoid this problem altogether in the future.]