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.]
digg
Stumble Upon
del.icio.us
Technorati
Comments 11
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 ¶Post a Comment