Undoing compression

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719935

    Comments posted to this topic are about the item Undoing compression

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71816

    Nice question, thanks Steve

    Compress & decompress are really handy functions...

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • Carlo Romagnano

    SSC-Insane

    Points: 21968

    The return type is VARBINARY(MAX) , so, it should be CASTed to a char type.
    Also, there's an undocumented function UNCOMPRESS.
    select UNCOMPRESS(decompress(COMPRESS('a')))
    Try: it returns the starting string.
  • Shayn Thomas

    SSCertifiable

    Points: 5603

    Cheers Steve,
    I havent used these yet so led to some good reading

    ---------------------------------------------------------------------------------------
    The more you know, the more you know that you dont know

  • primitivefuture2006

    Old Hand

    Points: 359

    Carlo Romagnano - Tuesday, November 6, 2018 1:18 AM

    The return type is VARBINARY(MAX) , so, it should be CASTed to a char type.
    Also, there's an undocumented function UNCOMPRESS.
    select UNCOMPRESS(decompress(COMPRESS('a')))
    Try: it returns the starting string.

    What is the difference between UNCOMPRESS() and DECOMPRESS()?

    Why even both using COMPRESS(), DECOMPRESS(), or UNCOMPRESS()?

    Thanks

  • Carlo Romagnano

    SSC-Insane

    Points: 21968

    primitivefuture2006 - Tuesday, November 6, 2018 6:01 AM

    Carlo Romagnano - Tuesday, November 6, 2018 1:18 AM

    The return type is VARBINARY(MAX) , so, it should be CASTed to a char type.
    Also, there's an undocumented function UNCOMPRESS.
    select UNCOMPRESS(decompress(COMPRESS('a')))
    Try: it returns the starting string.

    What is the difference between UNCOMPRESS() and DECOMPRESS()?

    Why even both using COMPRESS(), DECOMPRESS(), or UNCOMPRESS()?

    Thanks

    UNCOMPRESS seems CASTing VARBINARY(MAX) to a char type.
    string -> COMPRESS -> VARBINARY(MAX) -> DECOMPRESS -> VARBINARY(MAX) -> UNCOMPRESS -> string

  • Solomon Rutzky

    SSCoach

    Points: 16256

    Carlo Romagnano - Tuesday, November 6, 2018 6:13 AM

    UNCOMPRESS seems CASTing VARBINARY(MAX) to a char type.
    string -> COMPRESS -> VARBINARY(MAX) -> DECOMPRESS -> VARBINARY(MAX) -> UNCOMPRESS -> string

    I wouldn't exactly recommend using UNCOMPRESS as it seems to only work for VARCHAR, not NVARCHAR:

    SELECT UNCOMPRESS(CONVERT(VARBINARY(MAX), 'Hello')),
       UNCOMPRESS(CONVERT(VARBINARY(MAX), N'Bye'));
    -- Hello    B

    SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), 'Hello')),
       CONVERT(NVARCHAR(MAX), CONVERT(VARBINARY(MAX), N'Bye'));
    -- Hello    Bye

    As you can see, the UNCOMPRESS in the first query, when given Unicode data, cuts off after the first character because the "B" is 0x4200 instead of just 0x42. And the 0x00 is the null-terminator for strings, so it stops there.

    It also seems kinda silly that UNCOMPRESS even exists given that it only saves typing of 1 word: UNCOMPRESS vs CONVERT(VARCHAR(...).

    Take care, Solomon..

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • Solomon Rutzky

    SSCoach

    Points: 16256

    Carlo Romagnano - Tuesday, November 6, 2018 6:13 AM

    primitivefuture2006 - Tuesday, November 6, 2018 6:01 AM

    Carlo Romagnano - Tuesday, November 6, 2018 1:18 AM

    The return type is VARBINARY(MAX) , so, it should be CASTed to a char type.
    Also, there's an undocumented function UNCOMPRESS.
    select UNCOMPRESS(decompress(COMPRESS('a')))
    Try: it returns the starting string.

    What is the difference between UNCOMPRESS() and DECOMPRESS()?

    Why even both using COMPRESS(), DECOMPRESS(), or UNCOMPRESS()?

    Thanks

    UNCOMPRESS seems CASTing VARBINARY(MAX) to a char type.
    string -> COMPRESS -> VARBINARY(MAX) -> DECOMPRESS -> VARBINARY(MAX) -> UNCOMPRESS -> string

    Hi there. I have finally had time to do a proper analysis of the undocumented UNCOMPRESS function. What I found is that it is not doing what you think it is doing based on this very simply test (of just looking at a VARCHAR "a"). UNCOMPRESS assumes that every byte of input is actually a Unicode code point in the range of U+0001 - U+00FF (i.e. values 1 - 255). This is the range that has 0x00 as the first byte, and so the "compression" was to simply remove the 0x00 bytes. The UNCOMPRESS function merely adds the 0x00 byte back in for each byte of the input value. This is in no way associated with the COMPRESS / DECOMPRESS functions. For full details, please see my analysis of this function:

    "What Does the Undocumented UNCOMPRESS Function Do?"

    Take care,
    Solomon...

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply