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: 29% [?]
Related posts:
- ADO.Net SqlParameter and missing text in the output value
- COM Objects in SQL: Sending E-mail using SQL and CDONTS
- A simple asp:Repeater replacement for simple needs
- Everything you ever wanted to know about an enum (well, almost everything)
- ASP.Net, CDO, and ‘The transport failed to connect to the server.’
Uncategorized 
great solution !!!
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?
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.
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
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.
This is beautiful!!! You saved my project from the ash heap. Thank you!
nice article….
Thanks … You’ve saved me some thinking.
Excellent. Thanks.
Excellent solution.
This is so helpful. Thank you!
Very helpful. Saved my day.
Thanks.
Very Handy!
Thanks, worked like a charm.
thanks
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.
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).
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.
Good solution, helped me today!
You are a killer. Thx.
Yup, thats a cool handy solution.
But, what if my ntext column conains large data. In my case I have a column of datalength=112628 and many others that have datalenght near to it. Now, since nvarchar max length is 8000, how can I approach to this?
I am in an urgent need to replace my data in database. Any help would be appreciated!
Any ideas?
Arfan — the maximum data size of NVARCHAR(max) is 2^31-1 bytes — which is plenty more than 112,628.
If you’re still using SQL 2000, I’d suggest upgrading to SQL 2005 so you can use NVARCHAR(max) instead of VARCHAR(8000).
Works, Thanks
This is the perfect solution for my renaming issue. Thanks for posting!
Thank you! This works a treat (though really, how many hoops must one jump through when replacing text in an ntext field during an insert trigger
?)