SQL Server

Unicode Escape

Demonstrates options for unicode escaping non-us-ascii chars and emojis. 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 @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, 'LoadFile', @success OUT, 'qa_data/txt/utf16_emojis_accented_jap.txt', 'utf-16'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @sb, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sb
        RETURN
      END

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

    -- The above file contains the following text, which includes some emoji's,
    -- Japanese chars, and accented chars.

    -- 🧠
    -- 🔐
    -- ✅
    -- ⚠️
    -- ❌
    -- ✓
    -- 中
    -- é xyz à
    -- abc 私 は ん ghi

    DECLARE @crypt int
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT

    -- Charset is not used for unicode escaping.  Set it to "utf-8", but it means nothing.
    DECLARE @charsetNotUsed nvarchar(4000)
    SELECT @charsetNotUsed = 'utf-8'

    -- Indicate the desired format/style of Unicode escaping.
    -- Choose JSON-style (JavaScript-style) Unicode escape sequences by using "unicodeescape"
    DECLARE @encoding nvarchar(4000)
    SELECT @encoding = 'unicodeescape'

    DECLARE @escaped nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'EncodeString', @escaped OUT, @original, @charsetNotUsed, @encoding

    PRINT @escaped

    -- Output:
    -- \ud83e\udde0
    -- \ud83d\udd10
    -- \u2705
    -- \u26a0\ufe0f
    -- \u274c
    -- \u2713
    -- \u4e2d
    -- \u00e9 xyz \u00e0
    -- abc \u79c1 \u306f \u3093 ghi

    -- Revert back to the unescaped chars:
    DECLARE @unescaped nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'DecodeString', @unescaped OUT, @escaped, @charsetNotUsed, @encoding

    PRINT @unescaped

    -- -----------------------------------------------------------------------------------------
    -- Do the same, but use uppercase letters (A-F) in the hex values.
    SELECT @encoding = 'unicodeescape-upper'
    EXEC sp_OAMethod @crypt, 'EncodeString', @escaped OUT, @original, @charsetNotUsed, @encoding

    PRINT @escaped

    -- Output:
    -- \uD83E\uDDE0
    -- \uD83D\uDD10
    -- \u2705
    -- \u26A0\uFE0F
    -- \u274C
    -- \u2713
    -- \u4E2D
    -- \u00E9 xyz \u00E0
    -- abc \u79C1 \u306F \u3093 ghi

    -- Revert back to the unescaped chars:
    EXEC sp_OAMethod @crypt, 'DecodeString', @unescaped OUT, @escaped, @charsetNotUsed, @encoding

    PRINT @unescaped

    -- -----------------------------------------------------------------------------------------
    --  ECMAScript (JavaScript) “code point escape” syntax

    SELECT @encoding = 'unicodeescape-curly'
    EXEC sp_OAMethod @crypt, 'EncodeString', @escaped OUT, @original, @charsetNotUsed, @encoding

    PRINT @escaped

    -- Output:
    -- \u{d83e}\u{dde0}
    -- \u{d83d}\u{dd10}
    -- \u{2705}
    -- \u{26a0}\u{fe0f}
    -- \u{274c}
    -- \u{2713}
    -- \u{4e2d}
    -- \u{00e9} xyz \u{00e0}
    -- abc \u{79c1} \u{306f} \u{3093} ghi

    -- Revert back to the unescaped chars:
    EXEC sp_OAMethod @crypt, 'DecodeString', @unescaped OUT, @escaped, @charsetNotUsed, @encoding

    PRINT @unescaped

    -- -----------------------------------------------------------------------------------------
    -- Do the same, but use uppercase letters (A-F) in the hex values.
    SELECT @encoding = 'unicodeescape-curly-upper'
    EXEC sp_OAMethod @crypt, 'EncodeString', @escaped OUT, @original, @charsetNotUsed, @encoding

    PRINT @escaped

    -- Output:
    -- \u{D83E}\u{DDE0}
    -- \u{D83D}\u{DD10}
    -- \u{2705}
    -- \u{26A0}\u{FE0F}
    -- \u{274C}
    -- \u{2713}
    -- \u{4E2D}
    -- \u{00E9} xyz \u{00E0}
    -- abc \u{79C1} \u{306F} \u{3093} ghi

    -- Revert back to the unescaped chars:
    EXEC sp_OAMethod @crypt, 'DecodeString', @unescaped OUT, @escaped, @charsetNotUsed, @encoding

    PRINT @unescaped

    -- -----------------------------------------------------------------------------------------
    -- Unicode code point notation or U+ notation

    SELECT @encoding = 'unicodeescape-plus'
    EXEC sp_OAMethod @crypt, 'EncodeString', @escaped OUT, @original, @charsetNotUsed, @encoding

    PRINT @escaped

    -- Output:
    -- u+1f9e0
    -- u+1f510
    -- u+2705
    -- u+26a0u+fe0f
    -- u+274c
    -- u+2713
    -- u+4e2d
    -- u+00e9 xyz u+00e0
    -- abc u+79c1 u+306f u+3093 ghi

    -- Chilkat cannot unescape the Unicode code point notation or U+ notation.
    -- For this style, Chilkat only goes in one direction, which is to escape.

    -- To emit uppercase hex, specify unicodeescape-plus-upper
    SELECT @encoding = 'unicodeescape-plus-upper'
    -- ...
    -- ...

    -- -----------------------------------------------------------------------------------------
    -- HTML hexadecimal character reference

    SELECT @encoding = 'unicodeescape-htmlhex'
    EXEC sp_OAMethod @crypt, 'EncodeString', @escaped OUT, @original, @charsetNotUsed, @encoding

    PRINT @escaped

    -- Output:
    -- &#x1f9e0;
    -- &#x1f510;
    -- &#x2705;
    -- &#x26a0;&#xfe0f;
    -- &#x274c;
    -- &#x2713;
    -- &#x4e2d;
    -- &#xe9; xyz &#xe0;
    -- abc &#x79c1; &#x306f; &#x3093; ghi

    -- Revert back to the unescaped chars:
    EXEC sp_OAMethod @crypt, 'DecodeString', @unescaped OUT, @escaped, @charsetNotUsed, @encoding

    PRINT @unescaped

    -- -----------------------------------------------------------------------------------------
    -- HTML decimal character reference

    SELECT @encoding = 'unicodeescape-htmldec'
    EXEC sp_OAMethod @crypt, 'EncodeString', @escaped OUT, @original, @charsetNotUsed, @encoding

    PRINT @escaped

    -- Output:
    -- &#129504;
    -- &#128272;
    -- &#9989;
    -- &#9888;&#65039;
    -- &#10060;
    -- &#10003;
    -- &#20013;
    -- &#233; xyz &#224;
    -- abc &#31169; &#12399; &#12435; ghi

    -- Revert back to the unescaped chars:
    EXEC sp_OAMethod @crypt, 'DecodeString', @unescaped OUT, @escaped, @charsetNotUsed, @encoding

    PRINT @unescaped

    -- -----------------------------------------------------------------------------------------
    -- Hex in Angle Brackets

    SELECT @encoding = 'unicodeescape-angle'
    EXEC sp_OAMethod @crypt, 'EncodeString', @escaped OUT, @original, @charsetNotUsed, @encoding

    PRINT @escaped

    -- Output:
    -- <1f9e0>
    -- <1f510>
    -- <2705>
    -- <26a0><fe0f>
    -- <274c>
    -- <2713>
    -- <4e2d>
    -- <e9> xyz <e0>
    -- abc <79c1> <306f> <3093> ghi

    -- Chilkat cannot unescape the angle bracket notation.
    -- For this style, Chilkat only goes in one direction, which is to escape.

    EXEC @hr = sp_OADestroy @sb
    EXEC @hr = sp_OADestroy @crypt


END
GO