Batch update of database string content

Databases often encounter the need to replace some content in batches. Sometimes it is because there is no encoding when storing data, and sometimes it is because of poor information that must be replaced directly. I sorted out how to replace with an SQL statement: Replace the specified column content statement update [t_test] Set [detail] = Replace ([detail], 'hit XXX', 'new string ') note: This statement cannot replace ntext, except for ntext, all character types can be replaced. If you want to replace ntext fields, type conversion is required. Update [t_test] Set [detail] = Replace (convert (varchar (4000 ), [detail]), 'hit XXX', 'new string') Where id <4 Write a short SQL statement to complete replacement of the entire data table declare @ PTR varbinary (16)
Declare @ artid int
Declare @ position int, @ Len int
Set @ Len = datalength ('xxxa ')
Declare wux_cursor scroll cursor
For
Select textptr ([detail]), ID from t_test
For read only
Open wux_cursor
Fetch next from wux_cursor into @ PTR, @ artid
While @ fetch_status = 0
Begin
Select @ position = patindex ('% hits xxx %', [detail]) from t_test where id = @ artid
While @ position> 0
Begin
Set @ position = @ position-1
Updatetext [t_test]. [detail] @ PTR @ position @ Len 'xxxa'
Select @ position = patindex ('% hits xxx %', detail) from t_test where id = @ artid
End
Fetch next from wux_cursor into @ PTR, @ artid
End
Close wux_cursor
Deallocate wux_cursor
Go