SQL Server User Defined Function fnRegExp

I have never before seen some TSQL/COM automation method for calculating the Regular Expression result.

I am playing with this, here is the code:


CREATE FUNCTION dbo.fnRegExp
(
@source varchar(5000),
@regexp varchar(1000),
@ignorecase bit = 0
)
RETURNS bit
AS
–https://www.sqlteam.com/articles/regular-expressions-in-t-sql
/*

DECLARE @intLength AS INTEGER
DECLARE @vchRegularExpression AS VARCHAR(50)
DECLARE @vchSourceString as VARCHAR(50)
DECLARE @vchSourceString2 as VARCHAR(50)
DECLARE @bitHasNoSpecialCharacters as BIT

— Initialize variables
SET @vchSourceString = ‘Test one This is a test!!’
SET @vchSourceString2 = ‘Test two This is a test’

— Our regular expression should read as:
— [a-zA-Z ]{}
— eg. [a-zA-Z ]{10} … For a string of 10 characters

— Get the length of the string
SET @intLength = LEN(@vchSourceString)

— Set the completed regular expression
SET @vchRegularExpression = ‘[a-zA-Z ]{‘ +
CAST(@intLength as varchar) + ‘}’

— get whether or not there are any special characters
SET @bitHasNoSpecialCharacters = dbo.fnRegExp(@vchSourceString, @vchRegularExpression,0)

PRINT @vchSourceString
IF @bitHasNoSpecialCharacters = 1 BEGIN
PRINT ‘No special characters.’
END ELSE BEGIN
PRINT ‘Special characters found.’
END

PRINT ‘—‘

— Get the length of the string
SET @intLength = LEN(@vchSourceString2)

— Set the completed regular expression
SET @vchRegularExpression = ‘[a-zA-Z ]{‘ +
CAST(@intLength as varchar) + ‘}’

— get whether or not there are any special characters
SET @bitHasNoSpecialCharacters = dbo.fnRegExp(@vchSourceString2, @vchRegularExpression,0)

PRINT @vchSourceString2
IF @bitHasNoSpecialCharacters = 1 BEGIN
PRINT ‘No special characters.’
END ELSE BEGIN
PRINT ‘Special characters found.’
END

GO

*/
BEGIN
DECLARE @hr integer
DECLARE @objRegExp integer
DECLARE @objMatches integer
DECLARE @objMatch integer
DECLARE @count integer
DECLARE @results bit

EXEC @hr = sp_OACreate ‘VBScript.RegExp’, @objRegExp OUTPUT
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, ‘Pattern’, @regexp
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, ‘Global’, false
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, ‘IgnoreCase’, @ignorecase
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OAMethod @objRegExp, ‘Test’, @results OUTPUT, @source
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OADestroy @objRegExp
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
RETURN @results
END

https://www.sqlteam.com/articles/regular-expressions-in-t-sql