MSSQL Bulk Replacement text string

Source: Internet
Author: User
Tags mssql mssql server
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




Related Article

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.