SQL string replacement Handler

SQL string replacement Handler

Create function dbo. regexreplace
(
@ Source varchar (5000), -- original string
@ Regexp varchar (1000), -- Regular Expression
@ Replace varchar (1000), -- replace value
@ Globalreplace bit = 0, -- whether it is a global replacement
@ Ignorecase bit = 0 -- ignore size?
)
Returns varchar (1000)
Begin
Declare @ hr integer
Declare @ objregexp integer
Declare @ result varchar (5000)

Exec @ hr = sp_oacreate 'vbscript. regexp ', @ objregexp output
If @ hr <> 0 begin
Exec @ hr = sp_oadestroy @ objregexp
Return null
End
Exec @ hr = sp_oasetproperty @ objregexp, 'pattern', @ regexp
If @ hr <> 0 begin
Exec @ hr = sp_oadestroy @ objregexp
Return null
End
Exec @ hr = sp_oasetproperty @ objregexp, 'global', @ globalreplace
If @ hr <> 0 begin
Exec @ hr = sp_oadestroy @ objregexp
Return null
End
Exec @ hr = sp_oasetproperty @ objregexp, 'ignorecase ', @ ignorecase
If @ hr <> 0 begin
Exec @ hr = sp_oadestroy @ objregexp
Return null
End
Exec @ hr = sp_oamethod @ objregexp, 'replace ', @ result output, @ source, @ replace
If @ hr <> 0 begin
Exec @ hr = sp_oadestroy @ objregexp
Return null
End
Exec @ hr = sp_oadestroy @ objregexp
If @ hr <> 0 begin
Return null
End

Return @ result
End
Go

Call method: select dbo. regexreplace ('aa6bb4cc7c', 'D + ', 'A)
Output result: aaaabbaaccaac

Mysql tutorial string replacement function

The replace function can change a string in a field in batches.
Query the replace function in mysql.
Update 'xxx' set 'A' = replace ('A', 'replacement ', 'replacement') where xxx
=== Php tutorial china.com =========================================== ==================================
Update 'xxx' set 'A' = replace ('A', 'replacement ', 'replacement') where xxx
Update 'music' set 'file' = replace ('file', '', 'ddd ') where id <10
Update music set file = replace (file, '', 'def ') where id <10;
=== Mysql.com ============================================= ======================================
Replace the string with the replace function of mysql
For example, you want to replace the abc of the f1 field in Table tb1 with def.
Update tb1 set f1 = replace (f1, 'abc', 'def ');
Replace (str, from_str, to_str)
All the from_str strings in the str string are replaced by to_str, and the following string is returned:
Mysql> select replace ('www .111cn.com ', 'w', 'ww ');
-> 'Wwwwww .mysql.com'
This function is multi-byte secure.

Mysql replacement function 2

 

Mysql replace function is required for data conversion!

For example, you want to replace the abc of the f1 field in Table tb1 with def.

Update tb1 set f1 = replace (f1, 'abc', 'def ');

Replace (str, from_str, to_str)
All the from_str strings in the str string are replaced by to_str, and the following string is returned:
Mysql> select replace ('www .mysql.com ', 'w', 'ww ');
-> 'Wwwwww .mysql.com'
This function is multi-byte secure.

Example:
Update 'dede _ addonarticle' set body = replace (body,
'</Td> ',
'');
Update 'dede _ addonarticle' set body = replace (body,
'</Tr> ',
'');
Update 'dede _ addonarticle' set body = replace (body,
'<Tr> ',
'');
Update 'dede _ archives 'set title = replace (title,
'Dayang news -',
'');
Update 'dede _ addonarticle' set body = replace (body,
'../../../../../../',
'Http: // mb. bKjia. c0m ');

Mysql replace

Usage 1. replace intoreplace into table (id, name) values ('1', 'A'), ('2', 'bb ')
This statement inserts two records into the table.
2. replace (object, search, replace)
Replace all search objects with replaceselect replace ('www. bKjia. c0m', 'w', 'ww ') ---> www. bKjia. c0m