Posted By

pauliehaha on 02/09/09


Tagged


Versions (?)

FIND AND REPLACE IN TEXT COLUMN


 / Published in: SQL
 

  1. DECLARE @oldtext varchar(1000)
  2. DECLARE @newtext varchar(1000)
  3. DECLARE @textlen int
  4. DECLARE @ptr BINARY(16)
  5. DECLARE @pos int
  6. DECLARE @id uniqueidentifier
  7.  
  8. SET @oldtext = 'a'
  9. SET @newtext = 'b'
  10. SET @textlen = LEN(@oldtext)
  11.  
  12. DECLARE mycursor CURSOR LOCAL FAST_FORWARD
  13. FOR
  14. SELECT TEXTPTR([Text])
  15. ,CHARINDEX(@oldtext, [Text]) - 1
  16. FROM [dbo].[TABLE]
  17. WHERE [Text] LIKE '%' + @oldtext +'%'
  18.  
  19. OPEN mycursor
  20.  
  21. FETCH NEXT FROM mycursor INTO @ptr, @pos
  22.  
  23. WHILE @@fetch_status = 0
  24. BEGIN
  25. UPDATETEXT [TABLE].Text @ptr @pos @textlen @newtext
  26.  
  27. FETCH NEXT FROM mycursor INTO @ptr, @pos
  28. END
  29.  
  30. CLOSE mycursor
  31. DEALLOCATE mycursor

Report this snippet  

You need to login to post a comment.