syntax for updatetext

• ngadacz

  Grasshopper

  Points: 13

  More actions

  September 23, 2003 at 6:59 pm

  #81053

  hello,

  i would like to update a column called footer in a table called

  agency. i have to update about 60 different records in this table.

  footer is varchar(200)

  i would like to search for "<table bgcolor="#cccccc"" and replace it

  with "<table "

  there is stuff before and after the start of the table tag.

  i looked at the books online and could not get the syntax correct.

  am i using the right command? updatetext, or should i do this with a

  loop?

  thanks in advance.

  nicholas.gadacz

• bp

  SSCarpal Tunnel

  Points: 4656

  More actions

  September 23, 2003 at 10:43 pm

  #475528

  Look up REPLACE in BOL. It should do the trick. See below:

  --- cut here ---

  DECLARE @agency TABLE(footer varchar(8000))

  INSERT INTO @agency(footer) VALUES('<TABLE BGCOLOR=''#CCCCCC'' BORDER=0><TR><TD>Do you</TD><TR></TABLE><TABLE BORDER=0><TR><TD>mean</TD><TR></TABLE><TABLE BGCOLOR=''#CCCCCC'' BORDER=0><TR><TD>like this?</TD><TR></TABLE>');

  INSERT INTO @agency(footer) VALUES('<TABLE BGCOLOR=''#CCCCCC'' BORDER=0><TR><TD>Do you</TD><TR></TABLE><TABLE BORDER=0><TR><TD>mean</TD><TR></TABLE><TABLE BGCOLOR=''#CCCCCC'' BORDER=0><TR><TD>like this?</TD><TR></TABLE>');

  INSERT INTO @agency(footer) VALUES('<TABLE BGCOLOR=''#CCCCCC'' BORDER=0><TR><TD>Do you</TD><TR></TABLE><TABLE BORDER=0><TR><TD>mean</TD><TR></TABLE><TABLE BGCOLOR=''#CCCCCC'' BORDER=0><TR><TD>like this?</TD><TR></TABLE>');

  SELECT footer FROM @agency;

  SELECT REPLACE(footer,'<TABLE BGCOLOR=''#CCCCCC''','<TABLE') FROM @agency;

  --- cut here ---