I have hex strings that I need to convert to Base-2 binary strings, but I cannot get Impala to perform consistently.
E.g.
I would expect this statement:
select conv('0020008000',16,2) union
select conv('000006040A',16,2);
To return:
0000 0000 0010 0000 0000 0000 1000 0000 0000 0000
0000 0000 0000 0000 0000 0110 0000 0100 0000 1010
However, instead it's returning:
0000 0000 0010 0000 0000 0000 1000 0000 0000 0000
1100 0000 1000 0001 010
The 1st HEX value is converted correctly, but the 2nd is missing the first 21 digits (all zeros).
Can anyone explain why this is happening and how I can fix this behaviour?
Impala/Hive treats multiple leading zeros as redundant and trims them. I'm not sure if this behavior can be toggled on/off. I worked around it using lpad function.