SQL Server Requires Chilkat v11.1.0+

Regular Expression with Multiple Matches and Named Capture Groups

See more Regular Expressions Examples

Demonstrates regular expressions with named capture groups and multiple matches. SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    DECLARE @sb int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sb OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @crlf int
    SELECT @crlf = 1
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'Name: John Smith', @crlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'Name: Jack Johnson', @crlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'Name: Mary Adams', @crlf

    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    --  We have the following string:
    --  Name: John Smith
    --  Name: Jack Johnson
    --  Name: Mary Adams

    DECLARE @pattern nvarchar(4000)
    SELECT @pattern = 'Name:\s+(?<first>\w+)\s+(?<last>\w+)'
    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OASetProperty @json, 'EmitCompact', 0

    DECLARE @timeoutMs int
    SELECT @timeoutMs = 2000
    DECLARE @numMatches int
    EXEC sp_OAMethod @sb, 'RegexMatch', @numMatches OUT, @pattern, @json, @timeoutMs
    IF @numMatches < 0
      BEGIN
        --  Probably an error in the regular expression.
        --  Suggestion: Use AI to help create and/or diagnose regular expressions.
        EXEC sp_OAGetProperty @sb, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sb
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    --  Examine the matches:
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    --  Here is the JSON showing the matches.
    --  Important:  Capture group 0 always contains the entire match — that is, the portion of the input string that matches the full regular expression.

    --  {
    --    "named": {
    --      "first": 1,
    --      "last": 2
    --    },
    --    "match": [
    --      {
    --        "group": [
    --          {
    --            "cap": "Name: John Smith",
    --            "idx": 0,
    --            "len": 16
    --          },
    --          {
    --            "cap": "John",
    --            "idx": 6,
    --            "len": 4
    --          },
    --          {
    --            "cap": "Smith",
    --            "idx": 11,
    --            "len": 5
    --          }
    --        ]
    --      },
    --      {
    --        "group": [
    --          {
    --            "cap": "Name: Jack Johnson",
    --            "idx": 18,
    --            "len": 18
    --          },
    --          {
    --            "cap": "Jack",
    --            "idx": 24,
    --            "len": 4
    --          },
    --          {
    --            "cap": "Johnson",
    --            "idx": 29,
    --            "len": 7
    --          }
    --        ]
    --      },
    --      {
    --        "group": [
    --          {
    --            "cap": "Name: Mary Adams",
    --            "idx": 38,
    --            "len": 16
    --          },
    --          {
    --            "cap": "Mary",
    --            "idx": 44,
    --            "len": 4
    --          },
    --          {
    --            "cap": "Adams",
    --            "idx": 49,
    --            "len": 5
    --          }
    --        ]
    --      }
    --    ]
    --  }

    --  The capture group index is obtained by looking up the name in the JSON result.
    --  For example:

    DECLARE @idx_first int
    EXEC sp_OAMethod @json, 'IntOf', @idx_first OUT, 'named.first'
    DECLARE @idx_last int
    EXEC sp_OAMethod @json, 'IntOf', @idx_last OUT, 'named.last'

    DECLARE @i int
    SELECT @i = 0
    DECLARE @matchCount int
    EXEC sp_OAMethod @json, 'SizeOfArray', @matchCount OUT, 'match'
    WHILE @i < @matchCount
      BEGIN


        PRINT 'Match ' + @i + 1 + ':'
        EXEC sp_OASetProperty @json, 'I', @i

        EXEC sp_OASetProperty @json, 'J', @idx_first

        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'match[i].group[j].cap'
        PRINT 'first: ' + @sTmp0

        EXEC sp_OASetProperty @json, 'J', @idx_last

        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'match[i].group[j].cap'
        PRINT 'first: ' + @sTmp0


        PRINT ''
        SELECT @i = @i + 1
      END

    --  Output is: 

    --  Match 1:
    --  first: John
    --  first: Smith
    --  
    --  Match 2:
    --  first: Jack
    --  first: Johnson
    --  
    --  Match 3:
    --  first: Mary
    --  first: Adams

    EXEC @hr = sp_OADestroy @sb
    EXEC @hr = sp_OADestroy @json


END
GO