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