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