How to convert values in the string to hex in Hive

Question

I want to convert a string into hex, for example: there is a string as '[1,2,3,4,5]', I want to convert this to hex and the output should be in string as follow: '[31,32,33,35,35]' using hiveQL

Answer 1

Remove leading and trailing square brackets, split to array, explode, convert to HEX, then collect array again and finally concatenate it:

with data as (
select '[1,2,3,4,5]' str
)

select '['||concat_ws(',',collect_list(hex(e.a)))||']' as result
  from data d 
       lateral view explode(split(regexp_replace(str,'\\[|\\]',''),',')) e as a

result

[31,32,33,34,35]