REPLACE in a text column

• Miquel Martí

  Ten Centuries

  Points: 1217

  More actions

  January 30, 2006 at 10:21 am

  #111463

  Hello!

  I'v been seeking the net for days.

  Is there any way to do the same I do with REPLACE() and varchars, but over Text ???

  The problem of UPDATETEXT seems to be that the replacement has to be the same lenght that the replaced text

   

  Thanks again!!!!

• lucky-80472

  SSCertifiable

  Points: 7793

  More actions

  January 30, 2006 at 10:37 am

  #617776

  Hello Miquel,

  Please go through Books Online -> UPDATETEXT

  This can be used to change only a portion of a text column in place.

  Explanation with example is available.

  Thanks and have a nice day!!!

  
  Lucky

• Miquel Martí

  Ten Centuries

  Points: 1217

  More actions

  February 1, 2006 at 5:27 am

  #618077

  Thaks so much for your comments

  .... and patience

  You are right, REPLACE can be performed with UPDATETEXT

  Here's a little example for someone seekinf for the same info:

    SET @OldText = 'What the fuch!'

    SET @NewText = 'Whatever you may fuch!'

    SET @PtrText = ( SELECT TEXTPTR(TextField) FROM TABLE1)

    SET @Len     = LEN( @OldText )

    SET @Pos     = ( SELECT CHARINDEX(@OldText, TextField)-1 FROM TABLE1)

    WHILE @Pos <> -1

    BEGIN

      UPDATETEXT TABLE1.TextField @PtrText

                          @Pos

                          @Len

                          @NewText

      SET @Pos = ( SELECT CHARINDEX(@OldText, TextField)-1 FROM TABLE1 )

    END

   

  Allways a big pleasure