How can I use Presto sql and check if value of a column 'cola' is Hexadecimal? My goal is to resolve the issue of 'Not a valid base-16 number' when data is messy. I've tried below but not working.
regexp_like(cola, '^[^g-zG-Z]*$')
translate(upper(cola), '0123456789ABCDEF', '.') = '.'
Curious how do you check that in Presto? Thanks!
You can wrap the translation (via from_base
) into try
which will return null
if the translation fails:
-- sample data
WITH dataset(hex_string) as (
values ('0123456789ABCDEF'),
('0G')
)
-- query
select try(from_base(hex_string, 16)) IS NOT NULL is_hex
from dataset;
Output:
is_hex |
---|
true |
false |