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

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.

  1. Cast the NTEXT field to the NVARCHAR(max) datatype using the CAST function.
  2. Perform your REPLACE on the output of #1.
  3. 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.]

0 thoughts on “How to use REPLACE() within NTEXT columns in SQL Server

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

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

  • 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?

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

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

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

    • 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!

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

    • 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!!

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

  • 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!

  • 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!

Leave a Reply to Anonymous Cancel Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.