sides of march (Brian DeMarzo)

Thoughts on life, liberty, and information technology

How to use REPLACE() within NTEXT columns in SQL Server

27.2.2008

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 the NVARCHAR(max) datatype using the CAST 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.]

37 responses to “How to use REPLACE() within NTEXT columns in SQL Server”

h_slim

2008.02.28

great solution !!!
Thkx

LikeLike

Reply
Matt Barrett

2008.02.28

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?

LikeLike

Reply
Matt Barrett

2008.02.28

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.

LikeLike

Reply
harborpirate

2008.02.28

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

LikeLike

Reply
brian

2008.02.28

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.

LikeLike

Reply
Rich B

2008.11.13

This is beautiful!!! You saved my project from the ash heap. Thank you!

LikeLike

Reply
NVP

2008.11.25

nice article….

LikeLike

Reply
Thomas V

2009.02.20

Thanks … You’ve saved me some thinking.

LikeLike

Reply
dorusoft

2009.03.20

Excellent. Thanks.

LikeLike

Reply
watchmovies2008

2009.04.18

Excellent solution.

LikeLike

Reply
Loong

2009.06.03

This is so helpful. Thank you!

LikeLike

Reply
Jeffrey

2009.08.19

Very helpful. Saved my day.
Thanks.

LikeLike

Reply
Etienne – Kezber

2009.12.03

Very Handy!

Thanks, worked like a charm.

LikeLike

Reply
Andrew

2009.12.14

thanks 🙂

LikeLike

Reply
John Palmer

2010.01.14

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.

LikeLike

Reply
Sateesh Reddy

2010.01.21

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.

LikeLike

Reply
Saša Stanković

2010.02.18

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.

LikeLike

Reply
calvinhobbes

2010.04.09

Good solution, helped me today!

LikeLike

Reply
Drip

2010.04.15

You are a killer. Thx.

LikeLike

Reply
Arfan Baig

2010.05.25

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?

LikeLike

Reply
brian

2010.06.01

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).

LikeLike

Reply
Lukasz

2010.06.10

Works, Thanks

LikeLike

Reply
Karen

2010.06.11

This is the perfect solution for my renaming issue. Thanks for posting!

LikeLike

Reply
Nixta

2010.08.19

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 :)?)

LikeLike

Reply
eimear

2010.09.21

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.

LikeLike

Reply
1. brian
  
  2010.09.21
  
  If you are trying to update the database, you need to use an UPDATE statement.
  
  For example, this works:
  
  create table #test ( [desc] ntext ) insert into #test values ( '17'' x 14'' solid maple floor, recessed lights' ) select cast(replace(cast([desc] as nvarchar(max)),'''','') as ntext) from #test -- result is 17 x 14 solid maple floor, recessed lights -- the update statement would be: update props1 set [desc] = cast(replace(cast([desc] as nvarchar(max)),'''','') as ntext)
  
  Hope that helps!
  
  LikeLike
  
  Reply
Annabel

2010.10.25

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 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.

LikeLike

Reply
1. Kendra
  
  2015.01.06
  
  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!!
  
  LikeLike
  
  Reply
Gus Beare

2010.12.06

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!

LikeLike

Reply
william luu

2011.01.18

it works with me, but seem not to be right with the data: the output is trim to fit nvarchar max length.

LikeLike

Reply
brian

2011.01.18

@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.

LikeLike

Reply
Pixell

2011.08.03

Wow, thank you so much for sharing this. Worked create for clearing the ” characters in my data.

LikeLike

Reply
Marc

2011.09.07

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!

LikeLike

Reply
Anandarajeshwaran.J

2011.11.04

did wonders for my query, thanks a lot

LikeLike

Reply
Rajesh

2012.01.06

Thanks a lot. Worked great for me!

LikeLike

Reply
Steve

2015.04.30

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 &amp; Packing’, as you’d expect. Made me look clever, cheers!

LikeLike

Reply
Powsey

2022.11.09

Unfortunately, my TEXT fields cannot be converted to NVARCHAR or VARCHAR as they are large and exceed their 65,000 maximum number of characters. Therefore, this solution doesn’t help me unfortunately.

LikeLike

Reply