How to validate data is Hexadecimal in Presto

Question

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!

Guru Stron · Accepted Answer · 2025-01-05 13:43:24Z

2

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

answered Jan 5 at 13:43

Guru Stron

151k11 gold badges187 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Crubal Chenxi Li Jan 6 at 0:09

Sure and just upvoted.

Collectives™ on Stack Overflow

How to validate data is Hexadecimal in Presto

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related