HiveQL - How to find the column value is numeric or not using any UDF?
Use cast(expr as <type>)
. A null
is returned if the conversion does not succeed.
case when cast(col as double) is null then 'N' else 'Y' end as isNumber
or simply use Boolean expression in the WHERE: cast(col as double) is not null
Also you can create isNumber macro:
create temporary macro isNumber(s string)
cast(s as double) is not null;
And use it in your queries:
hive> select isNumber('100.100'), isNumber('100'), isNumber('.0'), isNumber('abc');
OK
_c0 _c1 _c2 _c3
true true true false
If you need to check for Integer then use cast(s as Int)
This approach works correctly with negative and fractional numbers.
I believe Hive supports rlike
(regular expressions). So, you can do:
where col rlike '[^0-9]'
This looks for any non-digit character. You can expand this, if your numeric values might have decimal points or commas.