(SQL Server) JSON Escape and Unescape a String

The encoding mode keyword "json" was added in Chilkat v9.5.0.66 to provide the ability to escape and unescape JSON strings. See Chilkat Encodings for the full list of encoding keywords, such as "base64", "hex", "url", etc.

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)
    -- Note: This example requires Chilkat version 9.5.0.66 or greater.

    DECLARE @sb int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sb OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @sb, 'Append', @success OUT, 'backslash: \\, A: \u0041'
    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    -- The output is:
    --    backslash: \\, A: \u0041

    -- Starting in Chilkat v9.5.0.66, the keyword "json" can be used
    -- to escape and unescape.  To escape is to encode.  To unescape is to decode.

    -- Unescape (decode) the string in sb.
    EXEC sp_OAMethod @sb, 'Decode', @success OUT, 'json', 'utf-8'
    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    -- After JSON unescaping:
    --    backslash: \, A: A

    -- Now JSON escape the string.
    EXEC sp_OAMethod @sb, 'Encode', @success OUT, 'json', 'utf-8'
    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    -- After JSON escaping:
    --    backslash: \\, A: A

    -- Note: The "A" is not escaped to "\u0041" because there is no need.
    -- The "\uXXXX" format would be produced for non-printable control chars.

    EXEC @hr = sp_OADestroy @sb


END
GO