need help in Updating ntext field

• L_avital

  Ten Centuries

  Points: 1379

  More actions

  February 19, 2009 at 11:57 am

  #73460

  Hello, I am trying to update a field that is ntext. I need to remove the last 13 charachters which are !!end-field!!. I tried the solution suggested here earlier

  CREATE TABLE #temp (rowid int,textcol ntext)

  INSERT INTO #temp values (1,'aaa bbb ccc ddd eee')

  DECLARE @from nvarchar(100), @to nvarchar(100), @pos int, @len int

  SET @from='ccc'

  SET @to='xxxxxx'

  SELECT @pos = CHARINDEX(@from,textcol)-1 FROM #temp WHERE rowid = 1

  SET @len = LEN(@from)

  DECLARE @ptrval binary(16)

  SELECT @ptrval = TEXTPTR(textcol) FROM #temp WHERE rowid = 1

  UPDATETEXT #temp.textcol @ptrval @pos @len @to

  SELECT * FROM #temp

  DROP TABLE #temp

  but it doesn't work for me get this error: Deletion length 13 is not in the range of available text, ntext, or image data.

  The statement has been terminated. This field datalenght is 20816, I cannot even output it completely in a query window. Any help is apreciated, LL

• The Dixie Flatline

  SSC Guru

  Points: 53353

  More actions

  February 19, 2009 at 5:20 pm

  #947276

  Try it this way. The example works, it just replaced the searched for value 'ccc' with 'xxxxxx'. To delete characters at the end, you find them and replace them with ''.

  CREATE TABLE #temp (rowid int,textcol ntext)

  INSERT INTO #temp values (1,'aaa bbb ccc ddd eee !!end field!!')

  DECLARE @from nvarchar(100), @to nvarchar(100), @pos int, @len int

  SET @from='!!end field!!'

  SET @to = '' --- don't replace with 'xxxxxx', replace with emptiness

  SELECT @pos = CHARINDEX(@from,textcol)-1 FROM #temp WHERE rowid = 1

  SET @len = LEN(@from)

  DECLARE @ptrval binary(16)

  SELECT @ptrval = TEXTPTR(textcol) FROM #temp WHERE rowid = 1

  UPDATETEXT #temp.textcol @ptrval @pos @len @to

  SELECT * FROM #temp

  DROP TABLE #temp

  __________________________________________________

  Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
  Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills