SQL calls Regular Expression udfs (csdn)

SQL call Regular Expression
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