Search and Replace script for Ntext field

I need a sql query / script that will search and replace a text string in 1 of the tables & fields of my database.  I know the DB, Table and Field name.  I just need to search and replace %SearchString% with %ReplaceString% (for example).

TIA!
dstjohnjrAsked:
Who is Participating?
 
BrandonGalderisiConnect With a Mentor Commented:
You have to cast the field as an nvarchar(max) first.
UPDATE yourtable 
  SET yourfield = REPLACE(cast(yourfield as nvarchar(max)), 'SearchString', 'ReplaceString')
WHERE yourfield LIKE '%SearchString%'

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you mean, soething like this:
UPDATE yourtable 
  SET yourfield = REPLACE(yourfield, 'SearchString', 'ReplaceString')
WHERE yourfield LIKE '%SearchString%'

Open in new window

0
 
dstjohnjrAuthor Commented:
Ok, the first one by angellll did not work.  It renders this error:

Msg 8116, Level 16, State 1, Line 1
Argument data type ntext is invalid for argument 1 of replace function.

Then, trying Brandons.  Better luck, but still only replaced two occurences of my string... when I know others exist.  Odd.  Hmmm....  Any ideas?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I indeed overlooked the NTEXT part, sorry.

now, with SQL 2008, you should NOT use NTEXT anymore, but NVARCHAR(MAX), and the UPDATE will work correctly.
0
 
BrandonGalderisiCommented:
No because the replace is a replace all, unless your db or server is set for case sensitive sort order.
0
 
BrandonGalderisiCommented:
If you are using case sensitive SQL, then it won't replace 'ABC' if you are searching for 'abc'.
0
 
dstjohnjrAuthor Commented:
I figured it out!  Here was my final code.  I was using an actual double quote character when it actually resided in my field as "

UPDATE [VERIATECH_SHARED_DNN].[dbo].[VDNN_HtmlText]
  SET [DesktopHtml] = REPLACE(cast([DesktopHtml] as nvarchar(max)), 'src="/Portals/0/', 'src="/Portals/VeriaTech/')
WHERE [DesktopHtml] LIKE '%src="/Portals/0/%'

Thanks for the assistance!
0
 
dstjohnjrAuthor Commented:
and for the record, this is in the latest version of DotNetNuke - freshly installed, version 4.9.0 I believe.  Was migrating a site from one server using SQL Server 2005 to our new server which uses SQL Server 2008.  Had some pathing issues I had to fix and didn't want to go through them one by one.
0
 
dstjohnjrAuthor Commented:
Thank you Brandon!  Great solution provided once I figured out the deal with the quote char (which actually resided in my db as ").
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.