Undoing compression

Question

Post reply

Undoing compression

Steve Jones - SSC Editor

SSC Guru

Points: 734434
More actions
November 5, 2018 at 10:59 pm

#77289

Comments posted to this topic are about the item Undoing compression

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

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

Stewart "Arturius" Campbell SSC Guru Points: 72497 More actions · Answer 1

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: 22599 More actions · Answer 2

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: 5863 More actions · Answer 3

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: 389 More actions · Answer 4

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: 22599 More actions · Answer 5

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: 16430 More actions · Answer 6

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 Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 7

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 Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR