sqlhexprestotrino

How to validate data is Hexadecimal in Presto


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!


Solution

  • 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