SQL SERVER Regular Replacement

Use PUBDB
Go

IF object_id (N ' dbo. Regexreplace ') is not NULL
DROP FUNCTION dbo. Regexreplace
GO
--Start creating a regular replacement function
CREATE FUNCTION DBO. Regexreplace
(
@string VARCHAR (MAX),--the string to be replaced
@pattern VARCHAR (255),--Replacement template
@replacestr VARCHAR (max),--the replaced string
@IgnoreCase INT = 0--0 Case-sensitive 1 is case insensitive
)
RETURNS VARCHAR (max)
As
BEGIN
DECLARE @objRegex INT, @retstr VARCHAR (8000)
--Create Object
EXEC sp_OACreate ' VBScript.RegExp ', @objRegex out
--Set properties
EXEC sp_OASetProperty @objRegex, ' Pattern ', @pattern
EXEC sp_OASetProperty @objRegex, ' IgnoreCase ', @IgnoreCase
EXEC sp_OASetProperty @objRegex, ' Global ', 1
-Execution
EXEC sp_OAMethod @objRegex, ' Replace ', @retstr out, @string, @replacestr
--Release
EXECUTE sp_OADestroy @objRegex
RETURN @retstr
END
GO

--to ensure normal operation, you need to set the OLE Automation procedures option to 1
EXEC sp_configure ' show advanced options ', 1
RECONFIGURE with OVERRIDE
EXEC sp_configure ' Ole Automation procedures ', 1
RECONFIGURE with OVERRIDE

SQL SERVER Regular Replacement