February 19, 2009 at 11:57 am
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
February 19, 2009 at 5:20 pm
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply