SQL Server 2005,2008 Regular expression substitution function application

CREATE functionDbo.regexreplace (@source ntext,--Original String@regexp varchar( +),--Regular Expressions@replace varchar( +),--Replace value@globalReplace bit = 1,--whether it is a global replacement@ignoreCase bit = 0 --whether to ignore uppercase and lowercase) ReturnS varchar( +) as begin Declare @hr integer Declare @objRegExp integer Declare @result varchar( the) exec @hr =sp_OACreate'VBScript.RegExp',@objRegExpOUTPUTIF @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',@resultOUTPUT,@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

Surface Area Configuration tools, surface Area Configuration tool, and features, OLE Automation: Support for OLE Automation, Microsoft SQL Server 2005

Microsoft SQL Server, on the server node, right-click, select Facets, and you can see a number of settings, one of which is the surface area Configurator

Use the example 1 code as follows:

declare @source nvarchar (4000)
Set @source = ' Dsafsdf '
Select Dbo.regexreplace (@source, ' \<[^\>]+\> ', ' ', 1, 1)

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

SQL Server 2005,2008 Regular expression substitution function application