SQL Server in-use expression encapsulation uses the

Encapsulated regular expression for SQL Server invocation

New scalar-valued function name----open database--programmability----scalar-valued functions

Use [Database]
GO
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo]. [Scalar value function name]

(

@source ntext,--the original string

@regexp varchar (1000),--Regular expression

@replace varchar (1000),--Replacement value

@globalReplace bit = 1,--whether it is a global substitution

@ignoreCase bit = 0-whether case is ignored

)

ReturnS varchar (+) 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

SQL Server in-use expression encapsulation uses the