SQL character substitution and substitution using SQL stored procedures

If you are replacing the specified string or character in the database tutorial, we need to take advantage of the SQL character substitution, but we may want to use the SQL stored procedure substitution for oversized data substitution because the stored procedure is more efficient than the replacement function we write ourselves.

Update data Table set field =replace (field, '. ', ')

Look at a query result character substitution

--select * from SJZ_DLFZB

--select Replace (substring (Tbrq,2,len (TBRQ)-2), ' $ ', '-' from SJZ_DLFZB

Update SJZ_DLFZB set Tbrq=replace (substring (Tbrq,2,len (TBRQ)-2), ' $ ', '-')

Update SJZ_HPZB set Wdm=replace (substring (Wdm,2,len (WDM)-2), ' $ ', '-' where hp_id!= ' 130121010001 '

Update SJZ_HPZB set Dxslx=replace (substring (Dxslx,2,len (DXSLX)-2), ' $ ', '-')

SELECT * FROM SJZ_NSLZB

Update SJZ_NSLZB set Tbrq=replace (substring (Tbrq,2,len (TBRQ)), ' $ ', '-' where nsl_id= ' 130131030061 '

SELECT * from SJZ_NSLZB where nsl_id= ' 130131030061 '

SELECT * from SJZ_NSLZB where nsl_id= ' 130132030033 '
Update SJZ_NSLZB Set Tbrq=replace (TBRQ, ' $ ', '-') where nsl_id= ' 130132030033 '
Sjz_xpzb

Update SJZ_XPZB set Wd=replace (substring (Wd,2,len (wd)), ' $ ', '-'

Replacing characters with stored procedures

CREATE TABLE hello (id int identity (1,1), content text)
INSERT INTO Hello
Select ' <br> '
UNION ALL SELECT ' <br& gt; '

--Defines a replacement/deletion string
DECLARE @s_str varchar (8000), @d_str varchar (8000)
Select @s_str = ' http://localhost/'--string to be replaced
, @d_str = '--replaced by a string

--defining cursors, looping data
DECLARE @id int
Declare #tb cursor FOR select IDs from Hello
Open #tb
FETCH NEXT from #tb into @id
While @ @fetch_status =0
Begin
--string substitution processing
DECLARE @p varbinary (), @postion int, @rplen int
Select @p=textptr (content), @rplen =len (@s_str), @postion =charindex (@s_str, content)-1 from Hello where id= @id
While @postion >0
Begin
UPDATETEXT hello.content @p @postion @rplen @d_str
Select @postion =charindex (@s_str, content)-1 from Hello where id= @id
End

FETCH NEXT from #tb into @id
End
Close #tb
Deallocate #tb

--Show results
SELECT * FROM Hello

--Delete data test environment
drop table Hello