DECLARE @word1 varchar(1000)
DECLARE @word2 varchar(1000)
DECLARE word_cursor CURSOR LOCAL fast_forward FOR
SELECT old_word, new_word FROM word_replace_table
OPEN word_cursor
FETCH NEXT FROM word_cursor INTO @word1, @word2
WHILE @@FETCH_STATUS = 0
BEGIN
SET xact_abort ON
BEGIN tran
DECLARE @otxt varchar(1000)
SET @otxt = @word1 /****/
DECLARE @ntxt varchar(1000)
SET @ntxt = @word2 /****/
DECLARE @txtlen int
SET @txtlen = len(@otxt)
DECLARE @ptr BINARY(16)
DECLARE @pos int
DECLARE @id int
DECLARE curs CURSOR LOCAL fast_forward
FOR
SELECT
productId,
textptr(description),
charindex(@otxt, description)-1
FROM
product
WHERE
description
LIKE
'%' + @otxt +'%'
OPEN curs
FETCH NEXT FROM curs INTO @id, @ptr, @pos
WHILE @@fetch_status = 0
BEGIN
print 'Text found in row id=' + cast(@id AS varchar) + ' at pos=' + cast(@pos AS varchar)
updatetext product.description @ptr @pos @txtlen @ntxt
FETCH NEXT FROM curs INTO @id, @ptr, @pos
END
CLOSE curs
DEALLOCATE curs
commit tran
FETCH NEXT FROM word_cursor INTO @word1, @word2
END
CLOSE word_cursor
DEALLOCATE word_cursor
Monday, April 24, 2006
ms sql for search-and-replace based on key/value table (not really tested)
Subscribe to:
Post Comments (Atom)


No comments:
Post a Comment