Regex Replace

IF OBJECT_ID(N'dbo.RegexReplace') IS NOT NULL
	DROP FUNCTION dbo.RegexReplace
GO

CREATE FUNCTION dbo.RegexReplace (
	@pattern VARCHAR(255)
	,@replacement VARCHAR(255)
	,@Subject VARCHAR(MAX)
	,@global BIT = 1
	,@Multiline BIT = 1
	)
RETURNS VARCHAR(MAX)
	/*The RegexReplace function takes three string parameters. The pattern (the regular expression) the replacement expression, and the subject string to do the manipulation to.

The replacement expression is one that can cause difficulties. You can specify an empty string '' as the @replacement text. This will cause the Replace method to return the subject string with all regex matches deleted from it (see "strip all HTML elements out of a string" below).
To re-insert the regex match as part of the replacement, include $& in the replacement text. (see "find a #comment and add a TSQL --" below)
If the regexp contains capturing parentheses, you can use backreferences in the replacement text. $1 in the replacement text inserts the text matched by the first capturing group, $2 the second, etc. up to $9. (e.g. see import delimited text into a database below) To include a literal dollar sign in the replacements, put two consecutive dollar signs in the string you pass to the Replace method.*/
AS
BEGIN
	DECLARE @objRegexExp INT
		,@objErrorObject INT
		,@strErrorMessage VARCHAR(255)
		,@Substituted VARCHAR(8000)
		,@hr INT
		,@Replace BIT

	SELECT @strErrorMessage = 'creating a regex object'

	EXEC @hr = sp_OACreate 'VBScript.RegExp'
		,@objRegexExp OUT

	IF @hr = 0
		SELECT @strErrorMessage = 'Setting the Regex pattern'
			,@objErrorObject = @objRegexExp

	IF @hr = 0
		EXEC @hr = sp_OASetProperty @objRegexExp
			,'Pattern'
			,@pattern

	IF @hr = 0 /*By default, the regular expression is case sensitive. Set the IgnoreCase property to True to make it case insensitive.*/
		SELECT @strErrorMessage = 'Specifying the type of match'

	IF @hr = 0
		EXEC @hr = sp_OASetProperty @objRegexExp
			,'IgnoreCase'
			,1

	IF @hr = 0
		EXEC @hr = sp_OASetProperty @objRegexExp
			,'MultiLine'
			,@Multiline

	IF @hr = 0
		EXEC @hr = sp_OASetProperty @objRegexExp
			,'Global'
			,@global

	IF @hr = 0
		SELECT @strErrorMessage = 'Doing a Replacement'

	IF @hr = 0
		EXEC @hr = sp_OAMethod @objRegexExp
			,'Replace'
			,@Substituted OUT
			,@subject
			,@Replacement

	/*If the RegExp.Global property is False (the default), Replace will return the @subject string with the first regex match (if any) substituted with the replacement text. If RegExp.Global is true, the @Subject string will be returned with all matches replaced.*/
	IF @hr <> 0
	BEGIN
		DECLARE @Source VARCHAR(255)
			,@Description VARCHAR(255)
			,@Helpfile VARCHAR(255)
			,@HelpID INT

		EXECUTE sp_OAGetErrorInfo @objErrorObject
			,@source OUTPUT
			,@Description OUTPUT
			,@Helpfile OUTPUT
			,@HelpID OUTPUT

		SELECT @strErrorMessage = 'Error whilst ' + COALESCE(@strErrorMessage, 'doing something') + ', ' + COALESCE(@Description, '')

		RETURN @strErrorMessage
	END

	EXEC sp_OADestroy @objRegexExp

	RETURN @Substituted
END
GO

--remove repeated words in text
SELECT  dbo.RegexReplace('b(w+)(?:s+1b)+', '$1',
                         'Sometimes I cant help help help stuttering',1, 1)

--find a #comment and add a TSQL --
SELECT  dbo.RegexReplace('#.*','--$&','
# this is a comment
first,second,third,fourth',1,1)

--replace a url with an HTML anchor
SELECT  dbo.RegexReplace(
        'b(https?|ftp|file)://([-A-Z0-9+&@#/%?=~_|!:,.;]*[-A-Z0-9+&@#/%=~_|])',
        '<a href="$2">$2</a>',
         'There is  this amazing site at http://www.simple-talk.com',1,1)

--strip all HTML elements out of a string
SELECT  dbo.RegexReplace('<(?:[^>''"]*|([''"]).*?1)*>',
   '','<a href="http://www.simple-talk.com">Simle Talk is wonderful</a><!--This is a comment --> we all love it',1,1)

--import delimited text into a database, converting it into insert statements
SELECT  dbo.RegexReplace(
 '([^|rn]+)[|rn]+([^|rn]+)[|rn]+([^|rn]+)[|rn]+([^|rn]+)[|rn]+',
 'Insert into MyTable (Firstcol,SecondCol, ThirdCol, Fourthcol)
select $1,$2,$3,$4
','1|white gloves|2435|24565
2|Sports Shoes|285678|0987
3|Stumps|2845|987
4|bat|29862|4875',1,1)
/*

Source: https://www.simple-talk.com/sql/t-sql-programming/tsql-regular-expression-workbench/