(SQL Server) Punycode Encoding / Decoding

Punycode is an encoding standard for representing Unicode characters using only the 7bit us-ascii characters that are permitted in network host names. Punycode is used for internationalized domain names -- i.e. IDN or IDNA (Internationalizing Domain Names in Applications).

Punycode is defined in RFC 3492. Converting to/from punycode does not include the "xn--" prefix. The "xn--" prefix is to signify that punycode follows. For example, the string " café.com" is converted to "caf-dma.com" in punycode. The punycode domain name is "xn--caf-dma.com".

Converting an email address to punycode would be as follows. Suppose the email address is "coffee@café.com". The punycode representation is "coffee@xn--caf-dma.com". The RFC 3492 punycode representation of "café.com" is simply "caf-dma.com", but the punycode domain name is "xn--caf-dma.com".

The "xn--" is a constant. It is the same regardless of the domain. For example, the punycode URL representation of "mañana.com" is "xn--maana-pta.com".

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 @success int

    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

    -- Load the string "café" from a utf-8 text file.
    EXEC sp_OAMethod @sb, 'LoadFile', @success OUT, 'qa_data/txt/cafe.txt', 'utf-8'

    EXEC sp_OAMethod @sb, 'PunyEncode', @success OUT
    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    EXEC sp_OAMethod @sb, 'PunyDecode', @success OUT
    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    -- The output is:
    -- 
    -- caf-dma
    -- café

    EXEC @hr = sp_OADestroy @sb


END
GO