--sql Regular substitution function
Copy Code code as follows:
CREATE function Dbo.regexreplace
(
@source ntext,--the original string
@regexp varchar (1000),--Regular expression
@replace varchar (1000),--Replacement value
@globalReplace bit = 1,--whether it is a global replacement
@ignoreCase bit = 0-whether or not to ignore case
)
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
/*
Configuring support for extended stored procedures
Microsoft SQL Server-> Configuration Tool-> Perimeter application configuration-> configuration-> OLE Automation: Support for OLE Automation
Use Example 1:
Copy Code code 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
*/
