Replace statement on ntext / text fields

• Anthony Main

  Grasshopper

  Points: 17

  More actions

  June 24, 2004 at 3:28 am

  #360159

  lo peeps

  in need of a solution to this problemo

  I need to carry out a replace such as:

  update reviews set review_body = replace(review_body, 'href="reviews', 'href="http://www.moddin.org.uk/reviews')

  on a table, however the column to be updated in this case review_body is of type ntext

  Unfortunately the data is > 8000 chars long so I cannot convert it to varchar 

  So does anyone have an idea of how I could do this over the contents of a whole table ?!?!

  Thanks in advanced, for anyone that scratches their head long enough

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  June 24, 2004 at 6:50 am

  #511767

  Hopefully the table has an ID column.

  DECLARE @reviewid int, @ptr binary(16), @idx int

  SET @reviewid = 0

  SELECT TOP 1 @reviewid = reviewid, @idx = PATINDEX('%href="reviews%',review_body)-1

  FROM reviews

  WHERE PATINDEX('%href="reviews%',review_body) > 0

  WHILE @reviewid > 0

  BEGIN

  SELECT @ptr = TEXTPTR(review_body)

  FROM reviews

  WHERE reviewid = @reviewid

  UPDATETEXT reviews.review_body @ptr @idx 13 'href="http://www.moddin.org.uk/reviews'

  SET @reviewid = 0

  SELECT TOP 1 @reviewid = reviewid, @idx = PATINDEX('%href="reviews%',review_body)-1

  FROM reviews

  WHERE reviewid > @reviewid

  AND PATINDEX('%href="reviews%',review_body) > 0

  END

  Far away is close at hand in the images of elsewhere.
  Anon.