September 8, 2003 at 10:45 am
Any suggestions on how to search and replace using an ntext column in a table?
September 9, 2003 at 7:53 am
BOL will give you details and examples of ntext search & replace using PATINDEX, DATALENGTH, TEXTPTR, UPDATETEXT etc.
Do you want to update the ntext col or use it in a search?
This will replace text in an ntext col.
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
Edited by - davidburrows on 09/09/2003 07:53:04 AM
Far away is close at hand in the images of elsewhere.
Anon.
September 9, 2003 at 2:55 pm
Per record in the same table, I need to replace every instance of a certain substring with a different value in an ntext column. Basically, I need to be able to do a REPLACE with an ntext (as if it were a varchar).
I was lazily hoping that someone had created their own procedure that could be modified and/or used.
September 10, 2003 at 3:15 am
Variation of above with a loop. You can wrap this in a proc and pass text.
CREATE TABLE #temp (rowid int,textcol ntext)
INSERT INTO #temp values (1,'aaa bbb ccc ddd eee')
INSERT INTO #temp values (2,'aaa bbb cc ddd eee')
INSERT INTO #temp values (3,'fff ggg ccc iii jjj')
DECLARE @from nvarchar(100), @to nvarchar(100), @pos int, @len int, @rowid int
DECLARE @ptrval binary(16)
SET @from='ccc'
SET @to='hhh'
SET @len = LEN(@from)
SET @rowid = 0
SELECT @rowid = rowid,@pos = CHARINDEX(@from,textcol)-1 FROM #temp WHERE CHARINDEX(@from,textcol) > 0
WHILE (@rowid > 0)
BEGIN
SELECT @ptrval = TEXTPTR(textcol) FROM #temp WHERE rowid = @rowid
UPDATETEXT #temp.textcol @ptrval @pos @len @to
SET @rowid = 0
SELECT @rowid = rowid,@pos = CHARINDEX(@from,textcol)-1 FROM #temp WHERE CHARINDEX(@from,textcol) > 0
END
SELECT * FROM #temp
DROP TABLE #temp
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply