(SQL Server) Decode JSON Unicode Escaping

Demonstrates how to decode JSON Unicode escaped strings.

JSON escaping with hexadecimal encoding, also known as "JSON Unicode escaping," is a way to represent special characters and non-ASCII characters within a JSON (JavaScript Object Notation) string using hexadecimal Unicode code points. This encoding is commonly used when you need to include characters that are outside the ASCII character set or when you want to represent characters in a more compact form.

In JSON Unicode escaping, special characters are represented using the '\u' escape sequence followed by a four-digit hexadecimal number that represents the Unicode code point of the character. Here are some examples:

• '\u0022': Represents a double quote ('"') character.
• '\u005C': Represents a backslash ('\') character.
• '\u003C': Represents a less-than ('<') character.
• '\u003E': Represents the greater-than ('>') character.

For example, if you have a JSON string containing '>' and '<', you would represent it using Unicode escape sequences like this:

Original String: ""
JSON Escaped String: "\u003chtml\u003e"

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 @s nvarchar(4000)
    SELECT @s = '\u003chtml\u003e'

    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, @s
    EXEC sp_OAMethod @sb, 'Decode', @success OUT, 'json', 'utf-8'

    DECLARE @sDecoded nvarchar(4000)
    EXEC sp_OAMethod @sb, 'GetAsString', @sDecoded OUT


    PRINT @sDecoded

    EXEC @hr = sp_OADestroy @sb


END
GO