SQL Server replacement function application

--sql Regular Replace function

The

Code is as follows:
CREATE function Dbo.regexreplace
(
@source ntext, the original string
@regexp varchar (1000),--Regular expression
@rep Lace varchar (1000),--Replaces the value
@globalReplace bit = 1, whether it is global replace
@ignoreCase bit = 0-Ignore case
)
ReturnS varcha R (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 br> 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< br> 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

/*
Configuring support for extended stored procedures
Microsoft SQL Server-> Configuration Tool-> Perimeter application configuration-> configuration-> OLE Automation: Support for OLE Automation

Use Example 1:
The code is as follows:
declare @source nvarchar (4000)
Set @source = ' Dsafsdf '
Select Dbo.regexreplace (@source, ' <[^>]+> ', ', ', 1, 1)

Use Example 2: (The database field contains <font color= ' #ff0000 ' >aaa</font> replace with <font>aaa</font>)
Select id,dbo.regexreplace (field, ' <font ([^>]) *> ', ' <font> ', 1,0) as Alias from table
*/