Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Search and Replace script for Ntext field

Posted on 2008-09-30
9
Medium Priority
?
439 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 143

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 2000 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 143

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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 course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

886 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