Template Script: RegEx\RegExSamples.sql

/*
-- FUNCTION DEFINITIONS

Util.RE.RegExEscape(@Input nvarchar(MAX))
RETURNS nvarchar(MAX)

Util.RE.RegExIndex(@Pattern nvarchar(4000), @Input nvarchar(MAX), @Options int)
RETURNS int

Util.RE.RegExIsMatch(@Pattern nvarchar(4000), @Input nvarchar(MAX), @Options int)
RETURNS bit

Util.RE.RegExMatch(@Pattern nvarchar(4000), @Input nvarchar(MAX), @Options int)
RETURNS nvarchar(MAX)

Util.RE.RegExMatches(@Pattern nvarchar(4000), @Input nvarchar(MAX), @Options int)
RETURNS TABLE (Match nvarchar(MAX) NULL,
    MatchIndex int NULL,
    MatchLength int NULL)

Util.RE.RegExOptionEnumeration(@IgnoreCase bit, @MultiLine bit, @ExplicitCapture bit, @Compiled bit, @SingleLine bit, @IgnorePatternWhitespace bit, @RightToLeft bit, @ECMAScript bit, @CultureInvariant bit)
RETURNS int

Util.RE.RegExReplace(@Input nvarchar(MAX), @Pattern nvarchar(4000), @Repacement nvarchar(MAX))
RETURNS nvarchar(MAX)

Util.RE.RegExReplaceX(@Pattern nvarchar(4000), @Input nvarchar(MAX), @Repacement nvarchar(MAX), @Options int)
RETURNS nvarchar(MAX)

Util.RE.RegExSplit(@Pattern nvarchar(4000), @Input nvarchar(MAX), @Options int)
RETURNS TABLE (Match nvarchar(MAX) NULL)
*/


--a few tests
---Is this card a valid credit card?
SELECT  Util.RE.RegExIsMatch('^(?:4[0-9]{12}(?:[0-9]{3})?|5[1-5][0-9]{14}|6(?:011|5[0-9][0-9])[0-9]{12}|3[47][0-9]{13}|3(?:0[0-5]|[68][0-9])[0-9]{11}|(?:2131|1800|35\d{3})\d{11})$',
                                    '4241825283987487', 1)
--is there a number in this string
SELECT  Util.RE.RegExIsMatch('\d', 'there is 1 thing I hate', 1)
--Verifies number Returns 1
DECLARE @pattern VARCHAR(255)
SELECT  @pattern = '[a-zA-Z0-9]\d{2}[a-zA-Z0-9](-\d{3}){2}[A-Za-z0-9]'
SELECT  Util.RE.RegExIsMatch(@pattern, '1298-673-4192', 1),
        Util.RE.RegExIsMatch(@pattern, 'A08Z-931-468A', 1),
        Util.RE.RegExIsMatch(@pattern, '[A90-123-129X', 1),
        Util.RE.RegExIsMatch(@pattern, '12345-KKA-1230', 1),
        Util.RE.RegExIsMatch(@pattern, '0919-2893-1256', 1)

/* Probably the simplest routines for a SQL programmer is the RegExReplace and the RegExIndex. This is because they work the same way as does the REPLACE() and PatIndex() */
SELECT  REPLACE('this is a revolting view', 'revolting', 'stunning')
SELECT  Util.RE.RegExReplace('this is a revolting view', 'revolting', 'stunning')
/* Both give the same result. However, with RegExReplace, we can use any RegEx pattern instead of a string
We'll create an insert script from a comma-delimited list*/
SELECT  Util.RE.RegExReplace('Sprocket,6.26,Paris
widget,2.476,London
Bucket,8.25,New Orleans', '^("[^"\r\n]*"|[^,\r\n]*),("[^"\r\n]*"|[^,\r\n]*),("[^"\r\n]*"|[^,\r\n]*)',
                                    'insert into MyTable select ''$1'',$2,''$3'' --$0')
/* which gives the result...
insert into MyTable select 'Sprocket',6.26,'Paris' --Sprocket,6.26,Paris
insert into MyTable select 'widget',2.476,'London' --widget,2.476,London
insert into MyTable select 'Bucket',8.25,'New Orleans' --Bucket,8.25,New Orleans


We have a powerful device here. We can put the string that matches the pattern into the result string using the $1, $2 $3 ... convention.

We can do exactly the same thing with RegExReplacex, which gives us a finer control over how the RegEx is executed. */

SELECT  Util.RE.RegExReplacex('^("[^"\r\n]*"|[^,\r\n]*),("[^"\r\n]*"|[^,\r\n]*),("[^"\r\n]*"|[^,\r\n]*)',
                                     'Sprocket,6.26,Paris
widget,2.476,London
Bucket,8.25,New Orleans', 'insert into MyTable select ''$1'',$2,''$3'' --$0',
                                     Util.RE.RegExOptionEnumeration(1, 1, 0, 0, 0, 0, 0, 0, 0))

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

--find a #comment and add a TSQL --
SELECT  Util.RE.RegExReplace('
# this is a comment
first,second,third,fourth', '#.*', '--$&')

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

--strip all HTML elements out of a string
SELECT  Util.RE.RegExReplace('Simle Talk is wonderful we all love it',
                                    '<(?:[^>''"]*|([''"]).*?\1)*>', '')


/*But there are a whole lot of other things we can do.
Let's return the first number in a string*/
SELECT  Util.RE.RegExMatch('\d+', ' somewhere there is a number 4567 and then more ', 1)
-- 4567
--escape a literal string so it can be part of a regular expression
SELECT  Util.RE.RegExEscape(' I might need to search for [*\\\*]')
/* this would become useful if you wanted to insert a literal string into a RegEx. How about, for example, you want to search a string for a substring where two words are near each other (at most four words apart) in either order.*/
DECLARE @String1 VARCHAR(80),
    @String2 VARCHAR(80),
    @RegEx VARCHAR(200)
SELECT  @String1 = Util.RE.RegExEscape('often'),
        @String2 = Util.RE.RegExEscape('wrong')
SELECT  @RegEx = '\b(?:' + @String1 + '(?:\W+\w+){0,4}?\W+' + @String2 + '|' + @String2 + '(?:\W+\w+){0,4}?\W'
        + @String1 + ')\b'
SELECT  Util.RE.RegExMatch(@RegEx,
                                  'A RegEx expression can often be wrong but it is usually possible to put it right.', 1)
--split a string into words
SELECT  *
FROM    Util.RE.RegExSplit('\W+', 'this is an exciting  regular   expression', 1)
--Find if the words 'Simple' and 'Talk' are within three words distant
SELECT  Util.RE.RegExIsMatch('\bsimple(?:\W+\w+){0,3}?\W+talk\b', 'It is simple to say that there is talk of
a wonderful website called Simple Talk', 1)
--Find the words 'Simple' and 'Talk' within three words distant
SELECT  Util.RE.RegExIndex('\bsimple(?:\W+\w+){0,3}?\W+talk\b', 'It is simple to say that there is talk of
a wonderful website called Simple Talk', 1)
/* we can return a table of every repeating word in a string (along with the index intyo the string and the length of the match) */
SELECT  *
FROM    Util.RE.RegExMatches('\b(\w+)\s+\1\b',--match any repeated word
                                    'i have had my ups and downs
but wotthehell wotthehell
yesterday sceptres and crowns
fried oysters and velvet gowns
and today i herd with bums
but wotthehell wotthehell
i wake the world from sleep
as i caper and sing and leap
when i sing my wild free tune
wotthehell wotthehell
under the blear eyed moon
i am pelted with cast off shoon
but wotthehell wotthehell', 3)

--get valid dates and convert to SQL Server format
SELECT DISTINCT
        CONVERT(DATETIME, MATCH, 103)
FROM    Util.RE.RegExMatches('\b(0?[1-9]|[12][0-9]|3[01])[- /.](0?[1-9]|1[012])[- /.](19|20?[0-9]{2})\b', '
12/2/2006 12:30 <> 13/2/2007
32/3/2007
2-4-2007
25.8.2007
1/1/2005
34/2/2104
2/5/2006', 1)


/* There are a number of ways we can use the RegExIsMatch function. Here are a few simple examples */
--is there a repeated word?
SELECT  Util.RE.RegExIsMatch('\b(\w+)\s+\1\b', 'this has has been repeated', 1)
--1
SELECT  Util.RE.RegExIsMatch('\b(\w+)\s+\1\b', 'this has not been repeated', 1)
--0

--Is the word 'for' and 'last' up to 2 words apart)
SELECT  Util.RE.RegExIsMatch('\bfor(?:\W+\w+){0,2}?\W+last\b', 'You have failed me for the last time, Admiral', 1)
--1
SELECT  Util.RE.RegExIsMatch('\bfor(?:\W+\w+){1,2}?\W+last\b',
                                    'You have failed me for what could be the last time, Admiral', 1)
--0

--is this likely to be a valid credit card?
SELECT  Util.RE.RegExIsMatch('^(?:4[0-9]{12}(?:[0-9]{3})?|5[1-5][0-9]{14}|6011[0-9]{12}|3(?:0
[0-5]|[68][0-9])[0-9]{11}|3[47][0-9]{13}|(?:2131|1800)\d{11})$', '4953129482924435', 1)
--IS this a valid ZIP code
SELECT  Util.RE.RegExIsMatch('^[0-9]{5,5}([- ]?[0-9]{4,4})?$', '02115-4653', 1)

--is this a valid Postcode?
SELECT  Util.RE.RegExIsMatch('^([Gg][Ii][Rr] 0[Aa]{2})|((([A-Za-z][0-9]{1,2})|(([A-Za-z][A-Ha
-hJ-Yj-y][0-9]{1,2})|(([A-Za-z][0-9][A-Za-z])|([A-Za-z][A-Ha-hJ-Yj-y][0-9]?[A-Za-z])))
) {0,1}[0-9][A-Za-z]{2})$', 'RG35 2AQ', 1)

--is this a valid European date?
SELECT  Util.RE.RegExIsMatch('^((((31\/(0?[13578]|1[02]))|((29|30)\/(0?[1,3-9]|1[0-2])))\/(1[
6-9]|[2-9]\d)?\d{2})|(29\/0?2\/(((1[6-9]|[2-9]\d)?(0[48]|[2468][048]|[13579][26])|((16
|[2468][048]|[3579][26])00))))|(0?[1-9]|1\d|2[0-8])\/((0?[1-9])|(1[0-2]))\/((1[6-9]|[2
-9]\d)?\d{2})) (20|21|22|23|[0-1]?\d):[0-5]?\d:[0-5]?\d$', '12/12/2007 20:15:27', 1)

--is this a valid currency value (dollar)?
SELECT  Util.RE.RegExIsMatch('^\$(\d{1,3}(\,\d{3})*|(\d+))(\.\d{2})?$', '$34,000.00', 1)

--is this a valid currency value (Sterling)?
SELECT  Util.RE.RegExIsMatch('^\£(\d{1,3}(\,\d{3})*|(\d+))(\.\d{2})?$', '£34,000.00', 1)

--A valid email address?
SELECT  Util.RE.RegExIsMatch('^(([a-zA-Z0-9!#\$%\^&\*\{\}''`\+=-_\|/\?]+(\.[a-zA-Z0-9!#\$%\^&
\*\{\}''`\+=-_\|/\?]+)*){1,64}@(([A-Za-z0-9]+[A-Za-z0-9-_]*){1,63}\.)*(([A-Za-z0-9]+[A
-Za-z0-9-_]*){3,63}\.)+([A-Za-z0-9]{2,4}\.?)+){1,255}$', 'Phil.Factor@simple-Talk.com', 1)

SELECT  Util.RE.RegExReplacex('^("[^"\r\n]*"|[^,\r\n]*),("[^"\r\n]*"|[^,\r\n]*),("[^"\r\n]*"|[^,\r\n]*)',
                                     'Sprocket,6.26,Paris
widget,2.476,London
Bucket,8.25,New Orleans', 'insert into MyTable select ''$1'',$2,''$3'' --$0',
                                     Util.RE.RegExOptionEnumeration(1, 1, 0, 0, 0, 0, 0, 0, 0))

Description for Template Script: RegEx\RegExSamples.sql

Todo
Site Map | Printable View | © 2008 - 2012 NuRoN Consulting, INC | Powered by mojoPortal | HTML 5 | CSS | Original design by Andreas Viklund
Share This Using Popular Bookmarking Services