Solved

Search and Replace script for Ntext field

Posted on 2008-09-30
9
408 Views
Last Modified: 2010-04-21
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!
0
Comment
Question by:dstjohnjr
  • 4
  • 3
  • 2
9 Comments
 
LVL 142

Expert Comment

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

Open in new window

0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 22609295
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
 

Author Comment

by:dstjohnjr
ID: 22609393
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22609432
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22609438
No because the replace is a replace all, unless your db or server is set for case sensitive sort order.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22609445
If you are using case sensitive SQL, then it won't replace 'ABC' if you are searching for 'abc'.
0
 

Author Comment

by:dstjohnjr
ID: 22609447
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
 

Author Comment

by:dstjohnjr
ID: 22609462
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
 

Author Closing Comment

by:dstjohnjr
ID: 31501726
Thank you Brandon!  Great solution provided once I figured out the deal with the quote char (which actually resided in my db as ").
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question