IF OBJECT_ID(N'dbo.ddf_RegexReplace') IS NOT NULL
DROP FUNCTION dbo.ddf_RegexReplace
GO
CREATE FUNCTION dbo.ddf_RegexReplace
(
@pattern VARCHAR(255),
@replacement VARCHAR(255),
@Subject VARCHAR(MAX),
@global BIT = 1,
@Multiline bit =1
)
RETURNS VARCHAR(MAX)
AS BEGIN
-- SELECT dbo.ddf_RegexReplace('[^a-zA-Z0-9]','','abcABC123. -=',1,1)
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.ddf_RegexReplace('\b(\w+)(?:\s+\1\b)+', '$1','Sometimes I cant help help help stuttering',1, 1)
--find a #comment and add a TSQL --
SELECT dbo.ddf_RegexReplace('#.*','--$&','# this is a comment first,second,third,fourth',1,1)
--replace a url with an HTML anchor
SELECT dbo.ddf_RegexReplace(
'\b(https?|ftp|file)://([-A-Z0-9+&@#/%?=~_|!:,.;]*[-A-Z0-9+&@#/%=~_|])',
'<a href="$2">$2</a>',
--strip all HTML elements out of a string
SELECT dbo.ddf_RegexReplace('<(?:[^>''"]*|([''"]).*?\1)*>',
--import delimited text into a database, converting it into insert statements
SELECT dbo.ddf_RegexReplace(
'([^\|\r\n]+)[|\r\n]+([^\|\r\n]+)[|\r\n]+([^\|\r\n]+)[|\r\n]+([^\|\r\n]+)[|\r\n]+',
'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)
*/