SQL Server text field string replacement example processing script

--SQL Server text field string replacement example processing script

/* -- Text field replacement
--*/
-- Create a data test environment
-- Create Table # Tb (AA text)
Declare @ s_str varchar (8000), @ d_str varchar (8000), -- Define the replacement string
@ P varbinary (16), @ postion int, @ rplen int, @ I _start int, @ I _end int

Select Identity (INT, 1, 1) as [ID], newsid into # From News
Select @ I _start = min ([ID]), @ I _end = max ([ID]) from #
While (@ I _start <= @ I _end)
Begin
-- Insert into # Tb (AA) Select Content from # Where [ID] = @ I _start
Select @ s_str = '\' -- string to be replaced
, @ D_str = '! '-- String to be replaced
-- String replacement
Select @ P = textptr (content), @ rplen = Len (@ s_str), @ postion = charindex (@ s_str, content) -1 from news where newsid in (select top 1 newsid from # Where [ID] = @ I _start)
While @ postion> 0
Begin
Updatetext news. Content @ P @ postion @ rplen @ d_str
Select @ postion = charindex (@ s_str, content)-1 from news where newsid in (select top 1 newsid from # Where [ID] = @ I _start)
End
-- Truncate table # TB

Select @ I _start = @ I _start + 1
End

-- Delete the data test environment
-- Drop table # TB
Drop table #