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 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
NTEXTfield to theNVARCHAR(max)datatype using theCASTfunction. - 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 with TEXT, and NVARCHAR with VARCHAR.]
[Note #2: 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.]
Popularity: 31% [?]
Comments 17
great solution !!!
Posted 28 Feb 2008 at 9:42 am ¶Thkx
Unless your replacing, on the server, a v. large amount of text with a relatively v. small amount of text, why would you want to embed this sort of processing in your SQL query?
Posted 28 Feb 2008 at 4:33 pm ¶Oh, and I’m asking because I’m genuinely curious, not because I think its a bad idea! But that casting has a performance overhead, and the management of that much business logic right down in the SQL has only even proven haphazard and painful in the systems I’ve worked on.
Posted 28 Feb 2008 at 4:52 pm ¶There is no question that Varchar(max) is a huge improvement over blob/image and text fields in usability. Keep in mind that searching vast amounts of text may still not be very fast, especially in items over 8000 characters, since these are not stored in the table, but in a separate overflow table similar to the way TEXT and IMAGE operate.
If you’re stuck on SQL 2000, first of all I pity you, secondly there is an article on SQL Team (one of the best T-SQL resources IMHO) about how to do search and replace in a TEXT column: http://www.sqlteam.com/article/search-and-replace-in-a-text-column
I also came across a nice article on Search SQL Server with a few details about varchar(max) that might be helpful to some: http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1098157,00.html
Posted 28 Feb 2008 at 5:31 pm ¶Would I use the above code in a demanding business process? No. However, it does help for situations where you need to solve a comparatively simple issue (finding/replacing text in NTEXT fields) without building a more complicated framework.
For example, this solution came up for me to help a client who had erroneously entered their server’s IP address instead of their domain name into hyperlinks in their content management system. Sure, I could have written a robust solution, but a quick SQL query to locate the IP addresses, then using queries like the ones I wrote about above to find/replace, gave us a 5-minute solution. Not glorious, but effective.
Obviously, if you needed something to run repeatedly or to be inclusive in your business logic, you’d want to do something more robust than this, especially if performance is a concern.
Posted 28 Feb 2008 at 6:28 pm ¶This is beautiful!!! You saved my project from the ash heap. Thank you!
Posted 13 Nov 2008 at 11:32 pm ¶nice article….
Posted 25 Nov 2008 at 7:50 am ¶Thanks … You’ve saved me some thinking.
Posted 20 Feb 2009 at 2:25 pm ¶Excellent. Thanks.
Posted 20 Mar 2009 at 9:44 am ¶Excellent solution.
Posted 18 Apr 2009 at 11:46 pm ¶This is so helpful. Thank you!
Posted 03 Jun 2009 at 11:07 pm ¶Very helpful. Saved my day.
Posted 19 Aug 2009 at 5:50 pm ¶Thanks.
Very Handy!
Thanks, worked like a charm.
Posted 03 Dec 2009 at 3:58 pm ¶thanks
Posted 14 Dec 2009 at 6:08 pm ¶Note that this is extremely useful as a find and replace to update a column on a given table. I was just hired to clean up a project after a nasty injection attack was performed, and being able to clean out the malicious javascript that had been inserted into the table was made easy via a mechanism like the above.
SQL UPDATE syntax with a replace on a column of type ntext, for those who need it, is here:
UPDATE myntexttable SET myntext = cast(replace(cast(myntext as nvarchar(max)),'find','replace') as ntext)
Always do a SELECT first to ensure the data will be modified as you expect it to be.
Posted 14 Jan 2010 at 1:46 pm ¶This is very very usefull solution which saved my time and got good repo from client. I was having a requirment where i ahev XML message as ntext in SQL table and i need to replace the encoding method from UTF-16 to ISO-8859-1. This solution really helped me. But this won’t work with SQL 2000 as it won’t support nvarchar(max).
Posted 21 Jan 2010 at 4:09 pm ¶ThanQ very much for the solution.
Many thanks to the Author of the post, and to the John Palmer for the Update SQL version which works great. I needed fast solution to update the links in the web site articles stored in database in a ntext format since the relative paths were changed.
Posted 18 Feb 2010 at 11:40 am ¶Post a Comment