SQL Server Requires Chilkat v11.1.0+

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
    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