(SQL Server) Compress and Decompress Hex String

Imagine we have data represented as a hex string. This example demonstrates how to decode, compress, and re-encode to a smaller hex string representing the compressed data. An even better choice is to re-encode to a more compact encoding such as base64.

Note: This example requires Chilkat v9.5.0.66 or greater.

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
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @success int

    DECLARE @strHex nvarchar(4000)
    SELECT @strHex = '54686520717569636B2062726F776E20666F78206A756D706564206F76657220746865206C617A7920646F672E0D0A54686520717569636B2062726F776E20666F78206A756D706564206F76657220746865206C617A7920646F672E0D0A54686520717569636B2062726F776E20666F78206A756D706564206F76657220746865206C617A7920646F672E0D0A54686520717569636B2062726F776E20666F78206A756D706564206F76657220746865206C617A7920646F672E0D0A54686520717569636B2062726F776E20666F78206A756D706564206F76657220746865206C617A7920646F672E0D0A0D0A'

    DECLARE @compress int
    -- Use "Chilkat_9_5_0.Compression" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Compression', @compress OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @compress, 'Algorithm', 'deflate'

    DECLARE @binDat int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @binDat OUT

    -- Load the hex string into a BinData object.
    -- This decodes the hexidecimal. The decoded bytes will be contained in the BinData.
    EXEC sp_OAMethod @binDat, 'AppendEncoded', @success OUT, @strHex, 'hex'

    -- Compress the BinData.
    EXEC sp_OAMethod @compress, 'CompressBd', @success OUT, @binDat

    -- Get the compressed data in hex format:
    DECLARE @compressedHex nvarchar(4000)
    EXEC sp_OAMethod @binDat, 'GetEncoded', @compressedHex OUT, 'hex'

    PRINT 'compressed hex:'

    PRINT @compressedHex

    -- The compressed hex is: 0BC94855282CCD4CCE56482ACA2FCF5348CBAF50C82ACD2D484D51C82F4B2D522801CAE72456552AA4E4A7EBF172850C61E5BC5C00

    -- Even better, get the compressed data in base64 format:
    -- (base64url and modbase64 are other valid choices...)
    DECLARE @compressedBase64 nvarchar(4000)
    EXEC sp_OAMethod @binDat, 'GetEncoded', @compressedBase64 OUT, 'base64'

    PRINT 'compressed base64:'

    PRINT @compressedBase64

    -- The compressed base64 is: C8lIVSgszUzOVkgqyi/PU0jLr1DIKs0tSE1RyC9LLVIoAcrnJFZVKqTkp+vxcoUMYeW8XAA=

    -- Now decompress:
    EXEC sp_OAMethod @binDat, 'Clear', @success OUT
    EXEC sp_OAMethod @binDat, 'AppendEncoded', @success OUT, @compressedHex, 'hex'
    EXEC sp_OAMethod @compress, 'DecompressBd', @success OUT, @binDat

    DECLARE @decompressedHex nvarchar(4000)
    EXEC sp_OAMethod @binDat, 'GetEncoded', @decompressedHex OUT, 'hex'

    PRINT 'decompressed hex:'

    PRINT @decompressedHex

    -- The output is the original hex string:
    -- 54686520717569636B2062726F776E20666F78206A756D706564206F76657220746865206C617A7920646F672E0D0A54686520717569636B2062726F776E20666F78206A756D706564206F76657220746865206C617A7920646F672E0D0A54686520717569636B2062726F776E20666F78206A756D706564206F76657220746865206C617A7920646F672E0D0A54686520717569636B2062726F776E20666F78206A756D706564206F76657220746865206C617A7920646F672E0D0A54686520717569636B2062726F776E20666F78206A756D706564206F76657220746865206C617A7920646F672E0D0A0D0A

    EXEC @hr = sp_OADestroy @compress
    EXEC @hr = sp_OADestroy @binDat


END
GO