(SQL Server) Mask Quoted Strings and then Restore Masked Strings

This example demonstrates how to mask the contents of single and/or double-quoted strings found in text, and then restore the masked strings.

Masking the contents of quoted strings before processing text is often necessary because the quoted portions can interfere with parsing, searching, or transforming the surrounding text. Here are common reasons:

1. Prevent false matches in searches

If you run a pattern match (e.g., regular expression) for a word or symbol, you might unintentionally match inside quoted strings where it’s not meant to apply.

Example:

Search: replace "cat" → "dog"
Text:   He said "the cat sat"  

Without masking, cat in the quotes would also be replaced.

2. Preserve literals during tokenization

When splitting text into tokens (e.g., programming language source, CSV data), quoted strings may contain spaces, commas, or special characters that should not be treated as delimiters.

3. Avoid accidental transformations

If performing case changes, formatting, or variable substitution, quoted strings often represent literal data that must remain unchanged.

4. Handle escaped characters correctly

Quotes can contain escaped sequences like \ or \\n that should be interpreted differently from unquoted text. Masking ensures they’re handled as literal text.

5. Maintain syntactic correctness

In source code or structured text, altering quoted strings can break syntax or meaning. Masking protects them during automated edits.

6. Simplify complex parsing logic

Instead of writing parsing rules that constantly check “am I inside quotes?”, masking them first allows you to process the rest of the text without quote-handling logic getting in the way.

Note: This example requires Chilkat v11.2.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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    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

    -- Here we have a string with some parts within single and double quotes.
    DECLARE @s nvarchar(4000)
    SELECT @s = 'This is "quoted text", empty string "", and ''another quoted string'', non-usascii "é", yada " yada yada.'

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

    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    PRINT 'Original text: ' + @sTmp0
    -- Original text: This is "quoted text", empty string "", and 'another quoted string', non-usascii "é", yada " yada yada.

    -- The MaskQuotedStrings method will mask quoted strings and store the contents of each masked string in a Chilkat StringTable.
    -- We can restore the masked strings by calling RestoreMaskedStrings.

    -- The quoteType can have the value 0, 1, or 2.
    -- 0: Mask both single-quoted and double-quoted strings.
    -- 1: Mask only single-quoted strings.
    -- 2: Mask only double-quoted strings.
    DECLARE @quoteType int
    SELECT @quoteType = 0

    -- Mask with the "_" char.
    DECLARE @st int
    EXEC @hr = sp_OACreate 'Chilkat.StringTable', @st OUT

    EXEC sp_OAMethod @sb, 'MaskQuotedStrings', @success OUT, '_', @quoteType, @st

    -- The quoted strings are now masked
    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    -- Result:
    -- This is "___________", empty string "", and '_____________________', non-usascii "__", yada " yada yada.

    -- A few notes:
    -- 
    -- 1) Empty quoted strings are ignored and won't have an entry in the output StringTable.
    -- 2) Non-us-ascii chars within double-quotes will increase length.  This is because the utf-8 byte represention of the string is masked.
    --    In this example, the é char is represented by 2 bytes in utf-8, and thus the us-ascii mask char occupies each byte.  
    --    (Only us-ascii printable chars can be used for the mask.)
    -- 3) Unclosed quotes are ignored.  In this example, there is a final double-quote that is unclosed.

    -- The output StringTable holds the contents of each masked string:
    DECLARE @count int
    EXEC sp_OAGetProperty @st, 'Count', @count OUT
    DECLARE @i int
    SELECT @i = 0
    WHILE @i < @count
      BEGIN

        EXEC sp_OAMethod @st, 'StringAt', @sTmp0 OUT, @i
        PRINT @i + ': ' + @sTmp0
        SELECT @i = @i + 1
      END

    -- Output:
    -- 0: quoted text
    -- 1: another quoted string
    -- 2: é

    -- Restore the masked strings:
    EXEC sp_OAMethod @sb, 'RestoreMaskedStrings', @success OUT, @quoteType, @st

    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    PRINT 'Restored: ' + @sTmp0

    -- Restored: This is "quoted text", empty string "", and 'another quoted string', non-usascii "é", yada " yada yada.

    EXEC @hr = sp_OADestroy @sb
    EXEC @hr = sp_OADestroy @st


END
GO