(SQL Server) Decode utf-8 Hex to a String

Converts a hex-encoded utf-8 bytes to a string.

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)


    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

    -- Append the hex.  The hex can be uppercase hex or lowercase hex, it doesn't matter.
    DECLARE @success int
    EXEC sp_OAMethod @sb, 'Append', @success OUT, 'D79ED795D7A6D7A8'

    -- Notice the above string contains Hebrew chars using the utf-8 byte representation.
    -- See https://www.loc.gov/marc/specifications/codetables/BasicHebrew.html
    -- For example:  D79E is the HEBREW LETTER MEM

    -- Therefore, when we decode to binary bytes, we must interpret the bytes as utf-8
    -- to get characters.

    -- Tell Chilkat to URL decode like this:
    EXEC sp_OAMethod @sb, 'Decode', @success OUT, 'hex', 'utf-8'

    -- sb now contains the decoded string.
    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @sb


END
GO