Regular Expression functions in the database

Regular Expression functions in the database

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 -- Case Insensitive?
)
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
Example

Select DBO. regexreplace (u_account, '[| = | region |-| [|] |: | [|. |/|' |? | "|! | □| _ |] | "|. | ^ |♂| + | ~ | ']', '', 1, 1) from userinfo where (u_account like '% [^ a-zA-Z0-9] % ')