(SQL Server) Regular Expression with Named Capture Groups

See more Regular Expressions Examples

See the sample code below.

Note: Chilkat uses PCRE2. See PCRE2 Regular Expressions
Also see: PCRE2 Performance

In PCRE2, named capture groups allow you to assign a name to a capturing group, making it easier to reference by name instead of number.

Syntax

(?<name>pattern)

or

(?'name'pattern)

Example

(?<first>\w+)\s+(?<last>\w+)

Applied to:

"John Smith"

Produces:

• first: John
• last: Smith

Note: This example requires Chilkat v11.1.0 or greater.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

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

    DECLARE @subject nvarchar(4000)
    SELECT @subject = 'John Smith'
    DECLARE @pattern nvarchar(4000)
    SELECT @pattern = '(?<first>\w+)\s+(?<last>\w+)'

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

    EXEC sp_OAMethod @sb, 'Append', @success OUT, @subject

    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": "John Smith",
    --           "idx": 0,
    --           "len": 10
    --         },
    --         {
    --           "cap": "John",
    --           "idx": 0,
    --           "len": 4
    --         },
    --         {
    --           "cap": "Smith",
    --           "idx": 5,
    --           "len": 5
    --         }
    --       ]
    --     }
    --   ]
    -- }

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

    EXEC sp_OAMethod @json, 'IntOf', @iTmp0 OUT, 'named.first'
    EXEC sp_OASetProperty @json, 'I', @iTmp0

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

    EXEC sp_OAMethod @json, 'IntOf', @iTmp0 OUT, 'named.last'
    EXEC sp_OASetProperty @json, 'I', @iTmp0

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

    -- Output is: 

    -- first: John
    -- last: Smith

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


END
GO