MSSQL Bulk Replacement text string

String

However, there are many existing systems that still have text-type fields, because the database structure cannot be modified for a variety of reasons.
However, we can use new SQL statements and stored procedures in the new method, for future MSSQL server discards specifically for the text and other types of operation functions after the trouble of modifying the program.
Here is a simple example of substitution,

String substitution for text type:

has a table T (id int not null,info text)
Required to replace ' ABC ' in info with ' 123 '
A typical stored procedure is written as:
drop procedure Dbo.procedure_1
Go
Set ANSI_NULLS on
SET QUOTED_IDENTIFIER ON
Go
CREATE PROCEDURE Dbo.procedure_1
As
DECLARE @ptr varbinary (16)
DECLARE @ID int
declare @Position int, @len int
Declare @strsrc char (3)
Declare @strdsc char (3)
Set @strtmp = ' abc '
Set @strdsc = ' 123 '
Set @len =3
DECLARE replace_cursor scroll Cursor
For
Select TEXTPTR ([info]), ID from T
For Read Only
Open Replace_cursor
FETCH NEXT from Replace_cursor into @ptr, @ID
While @ @fetch_status =0
Begin
Select @Position =patindex ('% ' + @strsrc + '% ', [info]) from T where id= @ID
While @Position >0
Begin
Set @Position = @Position-1
UPDATETEXT T.[info] @ptr @Position @len @strdsc
Select @Position =patindex ('% ' + @strsrc + '% ', [info]) from T where id= @ID
End
FETCH NEXT from Replace_cursor into @ptr, @ID
End
Close Replace_cursor
Deallocate replace_cursor
Go

which uses the text-specific function updatetext

Now we rewrite it as
drop procedure Dbo.procedure_1
Go

Set ANSI_NULLS on
SET QUOTED_IDENTIFIER ON
Go

CREATE PROCEDURE Dbo.procedure_1
As

DECLARE @ID int
DECLARE @strtmp varchar (max)
Declare @strsrc char (3), @strdsc char (3)
Set @strsrc = ' abc '
Set @strdsc = ' 123 '
DECLARE replace_cursor scroll Cursor
For
Select ID from TestTable
--for Read Only
Open Replace_cursor
FETCH NEXT from Replace_cursor into @ID
While @ @fetch_status =0
Begin
Select @strtmp = [INFO] from testtable where id= @ID
Select @strtmp = Replace (@strtmp, @strsrc, @strdsc)
Update T Set [INFO] = @strtmp where id= @ID
FETCH NEXT from Replace_cursor into @ID
End
Close Replace_cursor
Deallocate replace_cursor
Go

In this way, no matter the info field is changed to Char,nchar,text is good, the same can be universal