How To Update Multiple Occurrences Of A String In A Text Column (ntext)

• SSSolice

  SSCommitted

  Points: 1907

  More actions

  August 31, 2009 at 10:45 am

  #137706

  I have an ntext column that contains the full text of an rdl object. I need to update every occurrence of Parameters!AnyParm.Value with Parameters!AnyParm.Label.

  I could use Replace, Stuff, PatIndex, etc. to replace the first occurrence. Any help on how to change every occurrence in the column?

  "Parameters!" is always there. "Value" is always there. The "AnyParm" name will be different and occurs up to four or five times per row.

  Thanks.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  August 31, 2009 at 11:23 am

  #1047001

  Scott Solice (8/31/2009)

  ---

  I have an ntext column that contains the full text of an rdl object. I need to update every occurrence of Parameters!AnyParm.Value with Parameters!AnyParm.Label.

  I could use Replace, Stuff, PatIndex, etc. to replace the first occurrence. Any help on how to change every occurrence in the column?

  "Parameters!" is always there. "Value" is always there. The "AnyParm" name will be different and occurs up to four or five times per row.

  Thanks.

  text and ntext are a pain, which is why they brought out the varchar and nvarchar(max) types.

  the fast way would be to simply cast/convert to nvarchar(max), and use the replace function:

  both snippets below repalce every occurance of the string with the new one, for every row, if it iexists.

  UPDATE YOURTABLE

  SET YOURNTEXTCOLUMN = REPLACE(CONVERT(varchar(max),YOURNTEXTCOLUMN ),'Parameters!AnyParm.Value', 'Parameters!AnyParm.Label')

  the oldstyle way to do it was using this cursor as an example and using the UPDATETEXT function

  DECLARE

  @ItemID INT,

  @ptr BINARY(16), @idx INT,

  @oldString varchar(250),

  @newString varchar(250),

  @lenOldString int

  SET @oldString ='a href='

  SET @newString ='a href="MyPath/'

  SET @lenOldString=datalength(@oldString)

  --change the table Announcements to your table

  --change the column Description to your columnname

  SET @ItemID = 0

  --YOU must find and replace three object names:

  --ItemId--the PK of the table

  --Announcements.Description --the TEXT field to manipulate

  --Announcements --The Table Name to Manipulate

  SELECT TOP 1 @ItemID = ItemID, @idx = PATINDEX(@oldString,Announcements.Description)-1

  FROM Announcements

  WHERE PATINDEX(@oldString,Announcements.Description) > 0

  WHILE @ItemID > 0

  BEGIN

  SELECT @ptr = TEXTPTR(Announcements.Description)

  FROM Announcements

  WHERE ItemID = @ItemID

  UPDATETEXT Announcements.Announcements.Description @ptr @idx @lenOldString @newString

  SET @ItemID = 0

  SELECT TOP 1 @ItemID = ItemID, @idx = PATINDEX(@oldString,Announcements.Description)-1

  FROM Announcements

  WHERE ItemID > @ItemID

  AND PATINDEX(@oldString,Announcements.Description) > 0

  END

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!