UPDATETEXT error

• lena_d

  Old Hand

  Points: 306

  More actions

  January 4, 2006 at 1:51 am

  #103013

  Hi, i want to replace all instances of long string value within a TEXT column. But i got a error message

  "Deletion length 18 is not in the range of available text, ntext, or image data."

  This is a script

   

  while 1=1

        begin

           SELECT @ptrval_s = TEXTPTR(Text_Column) ,

                      @patptr=patindex('%123456789123456789',Text_Column)

           FROM MyTable  where Id=1

   

           if @patptr<0 break

   

           UPDATETEXT MyTable.Text_Column @ptrval_s @patptr 18 'xxx'

       end

   

  What is wrong? Thanks

• David Burrows

  SSC Guru

  Points: 65111

  More actions

  January 4, 2006 at 7:22 am

  #612597

  First insert_offset is zero based

  Second your patindex will only find occurrance if the column ends with 123456789123456789

  Try this

  WHILE 1=1

  BEGIN

    SELECT @ptrval_s = TEXTPTR(Text_Column),

      @patptr=PATINDEX('%123456789123456789%',Text_Column)-1

      FROM #MyTable  where Id=1

    IF @patptr < 0 BREAK

    UPDATETEXT #MyTable.Text_Column @ptrval_s @patptr 18 'xxx'

  END

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

• lena_d

  Old Hand

  Points: 306

  More actions

  January 4, 2006 at 7:30 am

  #612599

  Thank you, "-1" was a problem