SQL Server

Unicode Escape

Convert a string to Unicode escaped values. 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

    DECLARE @str nvarchar(4000)
    SELECT @str = 'bôn'
    DECLARE @charset nvarchar(4000)
    SELECT @charset = 'not_used'

    -- Unicode escape all chars using \uHHHH
    EXEC sp_OAMethod @sb, 'SetString', @success OUT, @str
    EXEC sp_OAMethod @sb, 'Encode', @success OUT, 'unicode-escape-all', @charset
    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0
    -- Output is \u0062\u00f4\u006e

    -- Unicode escape only 8bit chars using \uHHHH
    EXEC sp_OAMethod @sb, 'SetString', @success OUT, @str
    EXEC sp_OAMethod @sb, 'Encode', @success OUT, 'unicode-escape-8bit', @charset
    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0
    -- Output is b\u00f4n

    -- To use uppercase hex chars (A-F), add "-upper" to the encoding name
    EXEC sp_OAMethod @sb, 'SetString', @success OUT, @str
    EXEC sp_OAMethod @sb, 'Encode', @success OUT, 'unicode-escape-all-upper', @charset
    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0
    -- Output is \u0062\u00F4\u006E

    -- Unicode escape all chars using \u{HHHH}
    EXEC sp_OAMethod @sb, 'SetString', @success OUT, @str
    EXEC sp_OAMethod @sb, 'Encode', @success OUT, 'unicode-escape-all-curly-upper', @charset
    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0
    -- Output is \u{0062}\u{00F4}\u{006E}

    -- Unicode escape 8bit chars using HTML hex &#xH;
    EXEC sp_OAMethod @sb, 'SetString', @success OUT, @str
    EXEC sp_OAMethod @sb, 'Encode', @success OUT, 'unicode-escape-8bit-html-hex', @charset
    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0
    -- Output is b&#xf4;n

    -- Unicode escape all chars using HTML decimal &#D;
    EXEC sp_OAMethod @sb, 'SetString', @success OUT, @str
    EXEC sp_OAMethod @sb, 'Encode', @success OUT, 'unicode-escape-all-html-dec', @charset
    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0
    -- Output is &#98;&#244;&#110;

    -- Unicode escape all chars using u+HHHH
    EXEC sp_OAMethod @sb, 'SetString', @success OUT, @str
    EXEC sp_OAMethod @sb, 'Encode', @success OUT, 'unicode-escape-all-plus', @charset
    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0
    -- Output is u+0062u+00f4u+006e

    -- Unicode escape 8bit chars using angled brackets <HHHH>
    EXEC sp_OAMethod @sb, 'SetString', @success OUT, @str
    EXEC sp_OAMethod @sb, 'Encode', @success OUT, 'unicode-escape-8bit-angle-upper', @charset
    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0
    -- Output is b<00F4>n

    EXEC @hr = sp_OADestroy @sb


END
GO