SQL Server Regular Expression replacement function

-- SQL Regular Expression replacement function
Copy codeThe Code is as follows:
CREATE function dbo. regexReplace
(
@ Source ntext, -- original string
@ Regexp varchar (1000), -- Regular Expression
@ Replace varchar (1000), -- replace value
@ GlobalReplace bit = 1, -- whether it is a global replacement
@ IgnoreCase bit = 0 -- ignore case sensitivity
)
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

/*
Configure support for Extended Stored Procedures
Microsoft SQL Server 2005-> Configuration tool-> peripheral application configurator-> peripheral application configuration of the function-> Ole Automation: supports Ole Automation
Example 1:
Copy codeThe Code is as follows:
Declare @ source nvarchar (4000)
Set @ source = 'dsafsdf'
Select dbo. regexReplace (@ source, '\ <[^ \>] + \>', '', 1, 1)

Example 2: replace <font color = '# ff000000'> aaa </font> with <font> aaa </font> in the database field)
Select id, dbo. regexReplace (field, '<font ([^>]) *>', '<font>', 1, 0) AS Alias From table
*/