Possible to filter Spark dataframe by ISNUMERIC function?

Question

I have a DataFrame for a table in SQL. I want to filter this DataFrame if a value of a certain column is numeric or not.

val df = sqlContext.sql("select * from myTable");
val filter = df.filter("ISNUMERIC('col_a')");

I want filter to be a dataframe of df where the values in col_a are numeric.

My current solution doesn't work. How can I achieve this?

Answer 1

You can filter out as

df.filter(row => row.getAs[String]("col_a").matches("""\d+"""))

Hope this helps!

Answer 2

You can cast the field in question to DECIMAL and inspect the result:

filter("CAST(col_a AS DECIMAL) IS NOT NULL")

Optionally, you can pass length and/or precision to narrow down the valid numbers to a specific maximum length:

filter("CAST(col_a AS DECIMAL(18,8)) IS NOT NULL")