如何用Presto SQL验证列数据是否为十六进制？

问题背景 #

需要检查表中cola列的值是否为合法十六进制字符串，解决数据杂乱引发的Not a valid base-16 number错误，以下两种尝试方法无效：

regexp_like(cola, '^[^g-zG-Z]*$')
translate(upper(cola), '0123456789ABCDEF', '.') = '.'

正确验证方式 #

方法1：精准正则匹配

使用正则严格匹配仅包含十六进制字符（0-9、a-f、A-F）且非空的字符串：

regexp_like(cola, '^[0-9a-fA-F]+$')

• 说明：^和$锁定字符串首尾，[0-9a-fA-F]匹配单个合法十六进制字符，+确保至少有一个有效字符，直接排除空字符串和含非法字符的内容。

方法2：translate函数验证

通过translate将所有合法十六进制字符替换为空，若最终结果为空则说明原字符串全为合法字符：

translate(upper(cola), '0123456789ABCDEF', '') = ''

• 说明：先统一转为大写避免大小写差异，再移除所有合法十六进制字符，剩余内容为空即无非法字符；若需排除空字符串，可追加cola IS NOT NULL AND cola != ''条件。

方法3：try_cast容错验证

如果需要直接筛选出能正常转换为十六进制数值的记录，可借助try_cast捕获转换失败场景：

try_cast(from_base(cola, 16) AS BIGINT) IS NOT NULL

• 说明：from_base将十六进制字符串转为数值，try_cast会在转换失败时返回NULL，以此判定该值为非法十六进制内容。

内容的提问来源于stack exchange，提问作者Crubal Chenxi Li