Update an NText Column

Question

In my NText column, I have some data in between <script type='text/javascript'> </script> tags.

I want to replace all the data between <script type='text/javascript'> </script> tag with this data below:

<!--//<![CDATA[
   var m3_u = (location.protocol=='https:'?'https://d1.openx.org/ajs.php':'http://d1.openx.org/ajs.php');
   var m3_r = Math.floor(Math.random()*99999999999);
   if (!document.MAX_used) document.MAX_used = ',';
   document.write ("<scr"+"ipt type='text/javascript' src='"+m3_u);
   document.write ("?zoneid=946288&amp;source=_self&amp;target=_top&amp;block=1&amp;blockcampaign=1");
   document.write ('&amp;cb=' + m3_r);
   if (document.MAX_used != ',') document.write ("&amp;exclude=" + document.MAX_used);
   document.write (document.charset ? '&amp;charset='+document.charset : (document.characterSet ? '&amp;charset='+document.characterSet : ''));
   document.write ("&amp;loc=" + escape(window.location));
   if (document.referrer) document.write ("&amp;referer=" + escape(document.referrer));
   if (document.context) document.write ("&context=" + escape(document.context));
   if (document.mmm_fo) document.write ("&amp;mmm_fo=1");
   document.write ("'><\/scr"+"ipt>");
//]]>-->

How can I do it this. Please share the solution.

Update: Here's the string

<script src="http://d1.openx.org/ajs.php?zoneid=34288&amp;amp;source=_self&amp;amp;target=_top&amp;amp;block=1&amp;amp;blockcampaign=1&amp;amp;cb=98346855009&amp;amp;charset=unicode&amp;amp;loc=http%3A//www.mysite.com/Admin/AddEditArticle.aspx%3FID%3D623" type="text/javascript"></script>

Answer 1

What version of SQL Server are you using??

If you're on 2005 or up, try to avoid NTEXT and use NVARCHAR(MAX) instead - NVARCHAR(MAX) supports all the usual string operations, while NTEXT does not....

Also, NTEXT/TEXT/IMAGE are deprecated and will be removed from a future SQL Server version - so if you can, upgrade now! :-)

UPDATE: ok, so you cannot change the data type. Still: what version of SQL Server are you on?? 2000? 2005??

In 2005 and up, you could probably do something like:

DECLARE @YourOriginalText NVARCHAR(MAX)

SELECT @YourOriginalText = CAST(YourNTextColum AS NVARCHAR(MAX))
FROM dbo.YourTable
WHERE id = .......

// modify the NVARCHAR(MAX) variable
DECLARE @YourModifiedText NVARCHAR(MAX)

SET @YourModifiedText = REPLACE(@YourOriginalText, '....', '......')

// update the table after modification
UPDATE dbo.YourTable
SET ntextColumn = CAST(@YourModifiedText AS NTEXT)
WHERE id = ........

Marc