AWS Athena: Filter only numeric entries on a column

Question

I am trying to make a query on AWS Athena, where I want to filter only numeric entries from a varchar column. However, Athena does not support ISNUMERIC function. I saw some functions that would be useful, but they are available only for Amazon Redshift and not for AWS Athena. Is there any way to apply this type of filter on Athena? Thank you.

user_65458 · Answer

You can do this with try():

... 
WHERE try(CAST(my_column AS double)) IS NOT NULL

user_1109 · Answer

You can use regexp_like:

…
WHERE regexp_like(my_column, '^-?\d+$')

This would match positive and negative integers, if you want to match decimals you need a more complex pattern, of course.

AWS Athena: Filter only numeric entries on a column

2 Answers