SQL string Substitution handler function

SQL string Substitution handler function

Create function Dbo.regexreplace
(
@source varchar (5000),--Original string
@regexp varchar (1000),--Regular expression
@replace varchar (1000),--Replacement value
@globalreplace bit = 0,--whether it is a global replacement
@ignorecase bit = 0-is the size ignored?
)
Returns varchar (1000) AS
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+ ', ' AA ', 1, 1)
Output Result: AAAABBAACCAAC

MySQL Tutorial string substitution function

The Replace function allows you to bulk change a section of a string in a field.
Check the Replace function in MySQL.
Update ' xxx ' set ' a ' = replace (' A ', ' replace ', ' replace with ') where xxx
===php Tutorial china.com===============================================================
Update ' xxx ' set ' a ' = replace (' A ', ' replace ', ' replace with ') 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 a string with the Replace function of MySQL
For example, you want to replace the ABC of the F1 field in the table TB1 with Def
Update TB1 Set F1=replace (F1, ' abc ', ' Def ');
Replace (STR,FROM_STR,TO_STR)
All occurrences of the string from_str in string str are replaced by TO_STR, which then returns the string:
mysql> Select replace (' Www.111cn.net ', ' w ', ' ww ');
-> ' wwwwww.mysql.com '
This function is multibyte-safe.

MySQL Replacement function two

In the data conversion needs to use the MySQL replace function, here the basic introduction!

For example, you want to replace the ABC of the F1 field in the table TB1 with Def

Update TB1 Set F1=replace (F1, ' abc ', ' Def ');

Replace (STR,FROM_STR,TO_STR)
All occurrences of the string from_str in string str are replaced by TO_STR, which then returns the string:
mysql> Select replace (' www.mysql.com ', ' w ', ' ww ');
-> ' wwwwww.mysql.com '
This function is multibyte-safe.

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,
' Oceanic News-',
'' );
Update ' dede_addonarticle ' Set body = replace (body,
'.. /.. /.. /.. /.. /.. /',
' Http://mb.111cn.net ');

MySQL Replace

Usage 1.replace intoreplace into table (id,name) VALUES (' 1 ', ' AA '), (' 2 ', ' BB ')
The purpose of this statement is to insert two records into table tables.
2.replace (object, Search,replace)
Replace all occurrences of search in object with Replaceselect replace (' www.111cn.net ', ' w ', ' ww ')--->www www.111cn.net