SQL Server

Regular Expression with Capture Groups

See more Regular Expressions Examples

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

Demonstrates the following PCRE2 regular expression:

See the sample code below.

Name:\s+(\w+)\s+(\w+),\s+Email:\s+(\S+)

And apply it to this string:

Name: John Smith, Email: john.smith@example.com

---

Regex Components Explained

Part	Meaning	Matched Text
"Name:"	Matches the literal text "Name:"	"Name:"
"\s+"	Matches one or more whitespace characters (spaces, tabs, etc.)	(space)
"(\w+)"	Capture Group 1: One or more word characters ("a-zA-Z0-9_")	"John"
"\s+"	More whitespace	(space)
"(\w+)"	Capture Group 2: Another word (the last name)	"Smith"
","	A literal comma	","
"\s+"	Whitespace again	(space)
"Email:"	Matches the literal "Email:"	"Email:"
"\s+"	Whitespace	(space)
"(\S+)"	Capture Group 3: One or more non-whitespace characters	"john.smith@example.com"

---

Matches for Your Example String

String:

"Name: John Smith, Email: john.smith@example.com"

Regex Match Groups:

Group	Captured Value
Group 1	"John"
Group 2	"Smith"
Group 3	"john.smith@example.com"

---

Notes on Character Classes

• \w matches [a-zA-Z0-9_] — so it doesn’t include punctuation like a period.
• \S matches any non-whitespace character, so it’s good for capturing an email.

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
    -- 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 = 'Name: John Smith, Email: john.smith@example.com'
    DECLARE @pattern nvarchar(4000)
    SELECT @pattern = 'Name:\s+(\w+)\s+(\w+),\s+Email:\s+(\S+)'

    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

    -- This is the JSON with the match information.
    -- See the JSON parsing code below to get the matched capture group values.

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

    -- {
    --   "match": [
    --     {
    --       "group": [
    --         {
    --           "cap": "Name: John Smith, Email: john.smith@example.com",
    --           "idx": 0,
    --           "len": 47
    --         },
    --         {
    --           "cap": "John",
    --           "idx": 6,
    --           "len": 4
    --         },
    --         {
    --           "cap": "Smith",
    --           "idx": 11,
    --           "len": 5
    --         },
    --         {
    --           "cap": "john.smith@example.com",
    --           "idx": 25,
    --           "len": 22
    --         }
    --       ]
    --     }
    --   ]
    -- }

    DECLARE @cap nvarchar(4000)

    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
        DECLARE @j int
        SELECT @j = 0
        DECLARE @numCaptureGroups int
        EXEC sp_OAMethod @json, 'SizeOfArray', @numCaptureGroups OUT, 'match[i].group'
        WHILE @j < @numCaptureGroups
          BEGIN
            EXEC sp_OASetProperty @json, 'J', @j
            EXEC sp_OAMethod @json, 'StringOf', @cap OUT, 'match[i].group[j].cap'


            PRINT @j + ': ' + @cap
            SELECT @j = @j + 1
          END
        SELECT @i = @i + 1
      END

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

    -- Output

    -- Match 1:
    -- 0: Name: John Smith, Email: john.smith@example.com
    -- 1: John
    -- 2: Smith
    -- 3: john.smith@example.com

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


END
GO