SQL Server

Regular Expression Replace Capture Groups

See more Regular Expressions Examples

Demonstrates replacing capture groups for a regular expression.

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

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 = 'John Anders, +_+_+ Mary Robins $$$$'
    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

    -- There are 2 matches:

    -- {
    --   "named": {
    --     "first": 1,
    --     "last": 2
    --   },
    --   "match": [
    --     {
    --       "group": [
    --         {
    --           "cap": "John Anders",
    --           "idx": 0,
    --           "len": 11
    --         },
    --         {
    --           "cap": "John",
    --           "idx": 0,
    --           "len": 4
    --         },
    --         {
    --           "cap": "Anders",
    --           "idx": 5,
    --           "len": 6
    --         }
    --       ]
    --     },
    --     {
    --       "group": [
    --         {
    --           "cap": "Mary Robins",
    --           "idx": 19,
    --           "len": 11
    --         },
    --         {
    --           "cap": "Mary",
    --           "idx": 19,
    --           "len": 4
    --         },
    --         {
    --           "cap": "Robins",
    --           "idx": 24,
    --           "len": 6
    --         }
    --       ]
    --     }
    --   ]
    -- }

    -- To replace capture groups, write code to examine each capture group within
    -- each match, and provide a replacement string.
    -- Then call RegexReplace.

    -- For example, let's capitalize the first names, and add append "on" to the last name.
    -- After doing replacements, we should get:  JOHN Anderson, +_+_+ MARY Robinson $$$$

    DECLARE @firstNameIdx int
    EXEC sp_OAMethod @json, 'IntOf', @firstNameIdx OUT, 'named.first'
    DECLARE @lastNameIdx int
    EXEC sp_OAMethod @json, 'IntOf', @lastNameIdx OUT, 'named.last'

    DECLARE @sbTemp int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbTemp OUT

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

        EXEC sp_OASetProperty @json, 'I', @i

        -- The replacement string for the first name will be all uppercase.
        EXEC sp_OASetProperty @json, 'J', @firstNameIdx
        EXEC sp_OAMethod @sbTemp, 'Clear', NULL
        EXEC sp_OAMethod @json, 'StringOfSb', @success OUT, 'match[i].group[j].cap', @sbTemp
        EXEC sp_OAMethod @sbTemp, 'ToUppercase', @success OUT
        EXEC sp_OAMethod @json, 'UpdateSb', @success OUT, 'match[i].group[j].rep', @sbTemp

        -- Append "on" to the last name.
        EXEC sp_OASetProperty @json, 'J', @lastNameIdx
        EXEC sp_OAMethod @sbTemp, 'Clear', NULL
        EXEC sp_OAMethod @json, 'StringOfSb', @success OUT, 'match[i].group[j].cap', @sbTemp
        EXEC sp_OAMethod @sbTemp, 'Append', @success OUT, 'on'
        EXEC sp_OAMethod @json, 'UpdateSb', @success OUT, 'match[i].group[j].rep', @sbTemp

        SELECT @i = @i + 1
      END

    -- The JSON now has replacement strings:
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- {
    --   "named": {
    --     "first": 1,
    --     "last": 2
    --   },
    --   "match": [
    --     {
    --       "group": [
    --         {
    --           "cap": "John Anders",
    --           "idx": 0,
    --           "len": 11
    --         },
    --         {
    --           "cap": "John",
    --           "idx": 0,
    --           "len": 4,
    --           "rep": "JOHN"
    --         },
    --         {
    --           "cap": "Anders",
    --           "idx": 5,
    --           "len": 6,
    --           "rep": "Anderson"
    --         }
    --       ]
    --     },
    --     {
    --       "group": [
    --         {
    --           "cap": "Mary Robins",
    --           "idx": 19,
    --           "len": 11
    --         },
    --         {
    --           "cap": "Mary",
    --           "idx": 19,
    --           "len": 4,
    --           "rep": "MARY"
    --         },
    --         {
    --           "cap": "Robins",
    --           "idx": 24,
    --           "len": 6,
    --           "rep": "Robinson"
    --         }
    --       ]
    --     }
    --   ]
    -- }

    -- Call RegexReplace to update the StringBuilder with the replacements.
    EXEC sp_OAMethod @sb, 'RegexReplace', @success OUT, @json
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @sb, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sb
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @sbTemp
        RETURN
      END


    PRINT 'Result after doing replacements:'
    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    -- Result after doing replacements:
    -- JOHN Anderson, +_+_+ MARY Robinson $$$$

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


END
GO