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
NTEXT
field to theNVARCHAR(max)
datatype using theCAST
function. - Perform your
REPLACE
on 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.]
h_slim says:
great solution !!!
Thkx
Matt Barrett says:
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?
Matt Barrett says:
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.
harborpirate says:
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
brian says:
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.
Rich B says:
This is beautiful!!! You saved my project from the ash heap. Thank you!
NVP says:
nice article….
Thomas V says:
Thanks … You’ve saved me some thinking.
dorusoft says:
Excellent. Thanks.
watchmovies2008 says:
Excellent solution.
Loong says:
This is so helpful. Thank you!
Jeffrey says:
Very helpful. Saved my day.
Thanks.
Etienne - Kezber says:
Very Handy!
Thanks, worked like a charm.
Andrew says:
thanks 🙂
John Palmer says:
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.
Sateesh Reddy says:
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.
Saša Stanković says:
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.
calvinhobbes says:
Good solution, helped me today!
Drip says:
You are a killer. Thx.
Arfan Baig says:
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?
brian says:
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).
Lukasz says:
Works, Thanks
Karen says:
This is the perfect solution for my renaming issue. Thanks for posting!
Nixta says:
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 :)?)
eimear says:
hi, i want to do something similar but am just not able to crack it at the moment, can anyone offer a solution? i have a table named props1, there is a desc field in this table. this fields data type is ntext. i want to replace all occurances of curly quotes with straight quotes in the field. i am running the following query:
select cast(replace(cast(desc as nvarchar(max)),’’’,””) as ntext)
from props1
when i run it it says query executed successfully but the text still contains curly quotes:
17’ x 14’ solid maple floor, recessed lights
i would appreciate any help.
thanks.
brian says:
If you are trying to update the database, you need to use an UPDATE statement.
For example, this works:
Hope that helps!
Annabel says:
I found another way as long as you are in 2005 or above.
I have a database with an ntext field (it’s not my database and so cannot alter the definition of the field). This field contained HTML data (well XML data really, defining a custom data definition). At any rate I had some poorly formed XML in some records and needed to do a replace. Naturally NTEXT would not allow the replace. So, here’s what I did
Created a table called mytable with an ID field and a varchar(max) field, inserted the records from the first table (with NTEXT field) where the XML was no good. Did my replace in mytable, then joined then back together on the ID field doing the update back to the NTEXT field from the corrected varchar(max) field
CREATE TABLE [dbo].[mytable](
[id] [bigint] NULL,
[html] [varchar](max) NULL
) ON [PRIMARY]
insert mytable
select id,html from tablewithbadhtml
where html like ‘%badlyformed xml%’
update mytable
set html = replace(html,’bad_bit_of_xml’,’good_bit_of_xml’)
update t
set html = m.html
from tablewithbadhtml t inner join mytable m on m.id = t.id
It works for me because mine is a one off fix, but you could use it in othr processing by using #tmp tables perhaps. Depending on what you are trying to do, this may be efficient or it may not be.
Kendra says:
THank you so much! I was having the hardest time grabbing data from SQL and throwing it into Excel to then do a data migration project due to carriage returns/extra lines in an ntext field. It’s not my database so I couldn’t do much to it, but I did do the update query and it worked!!!! THANK YOU!!
Gus Beare says:
thanks a lot for this it really got me out of a hole. I needed to replace a domain name in all the content of a very large web site in dotnetnuke and this got me there.
thanks very much for posting it!
william luu says:
it works with me, but seem not to be right with the data: the output is trim to fit nvarchar max length.
brian says:
@william luu: Unfortunately, the output will be trimmed to the max length. If you are not using any Unicode characters, you can use VARCHAR(8000) instead of NVARCHAR(4000) to give you more flexibility. To be safe, check then length of your TEXT/NTEXT columns first using the DATALENGTH() function so you can avoid unexpected truncation.
Pixell says:
Wow, thank you so much for sharing this. Worked create for clearing the ” characters in my data.
Marc says:
Very helpful. I migrated a script from Access to MS SQL and ran into this issue. Funny that migrating the CSV file from Access didn’t have a problem with extra double-quotes, but exporting from SQL did. Thankfully your post here saved my bacon. Thanks!
Anandarajeshwaran.J says:
did wonders for my query, thanks a lot
Rajesh says:
Thanks a lot. Worked great for me!
Steve says:
In Sage CRM, needed to remove spurious characters (‘amp;’) entered into a freetext line in a quote. This did it perfectly, thanks for the advice:
Update dbo.QuoteItems
set QuIt_description = cast(REPLACE(cast(quit_description as nvarchar(max)),’amp;’,”) as ntext)
where QuIt_description like ‘%amp;%’
Even works where there are multiple ‘amp;’ entries, ie ‘Carriage & Packing’, as you’d expect. Made me look clever, cheers!