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
TEXTfield to theNVARCHAR(max)datatype using theCASTfunction. - Perform your
REPLACEon 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.]
