Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
scouse
Starting Member
2 Posts |
 Posted - 2007-09-03 : 05:04:54
|
Hi, I'm new here and not sure if this is the right place to post but...I have a ntext field in a database that is full of content for a website. However, the website was originally run on a test domain (www.domain1.co.uk) and a load of the links have www.domain1.co.uk/whatever.asp. I now want to run an SQL statement to go through and replace all instances of www.domain1.co.uk/ with www.domain2.co.uk/.I tried this:update PAGE1set CONTENTAREA = replace(CONTENTAREA, 'http://www.domain1.co.uk/', 'http://www.domain2.co.uk/') but this produces this error:Argument data type ntext is invalid for argument 1 of replace function.Is there another way I can do this?Thanks |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-03 : 05:23:59
|
Use the UPDATETEXT method.quote:
DECLARE @ptrval binary(16)SELECT @ptrval = TEXTPTR(pr_info) FROM pub_info pr, publishers p WHERE p.pub_id = pr.pub_id AND p.pub_name = 'New Moon Books'UPDATETEXT pub_info.pr_info @ptrval 88 1 'b';
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
scouse
Starting Member
2 Posts |
Posted - 2007-09-03 : 05:30:57
|
Thanks very much, but could you just explain that snippet of code you posted as I'm having difficulty understanding which bits I need to change  |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-03 : 07:57:20
|
| I don't know how to replace in NTEXT - unless the Text is < 4,000 characters, in which case you could do:set CONTENTAREA = CONVERT(text, REPLACE(CONVERT(nvarchar, 4000),'http://www.domain1.co.uk/', 'http://www.domain2.co.uk/'))Personally I would remove the domain name altogether - just start with "/" - so that it works on either the Test domain or the Live one, or any future domain!Kristen |
|
|
|
|
|
|
|
|
replacing entries in a database