AWS Athena How to check for numeric value before multiplying the value

Question

In Athena, we have column which is a varchar column (don't know why it's a varchar column when it should be a numeric column) that we need to multiply its values by 3.

All the values of the column are numeric, except for 1 row that mistakenly has non numeric value.

How can I multiply the column value by 3 only for those rows whose value is numeric ? Or how how I check if the row value is numeric before multiplying it by 3 ?

select cast(myColumn as decimal(11,2))*3 from myDatabase.myTable -> this will throw an error on the non numeric value "Cannot cast VARCHAR '5/1/2020 AU' to DECIMAL(11, 2). Value is not a number."

Something like this in SQL Server where it will return 0 if it encounters an error will do, but how can I do it in Athena ?

select coalesce(try_parse(myColumn as decimal(11,2) using 'en-GB'),'0')*3 from myDatabase.myTable

Thank you

Answer 1

You can use try_cast:

try_cast(value AS type) → type
Like cast(), but returns null if the cast fails.

For example:

select try_cast(myColumn as decimal(11,2)) * 3 
from myDatabase.myTable