ISNUMERIC Function?

[Josh]

Hi. SQL Server has a function named ISNUMERIC that can be used to determine whether an expression is a valid numeric type. Is there a function like this in Vertica?

[JimKnicely]

Hi Josh,

There isn't a built in function like ISNUMERIC, but you can create your own.

Example:

Code: Select all

dbadmin=> create function isnumeric(x varchar) return varchar
dbadmin-> as
dbadmin-> begin
dbadmin->   return REGEXP_COUNT(x, '^[0-9.-]+$');
dbadmin->
dbadmin-> end;
CREATE FUNCTION
dbadmin=> select col1, isnumeric(col1) isnumeric from test;
 col1 | isnumeric
------+-----------
 100  | 1
 25.5 | 1
 A    | 0
 25A  | 0
 -60  | 1
(5 rows)

Have fun!

[rajasekhart]

Hi Knicely87,

Thats the great way of thinking.
It is very helpful to me.

Thanks a lot

[sgwaltney3]

The ISNUMERIC function written above is not entirely accurate.

It will identify non-numeric strings as numeric and numeric strings as non-numeric.

Example: '....'
This is not a numeric string and will not be recognized as such by Vertica (ie CAST('....' as DOUBLE PRECISION)).
However the regular expression will match this.

Example: '+1.98E2'
Vertica supports an optional plus ("+") or minus sign ("-") as well as decimal exponents. Therefore, this is a valid numeric string with the value of 198.
However the regular expression will not match this.

To accurately identify strings Vertica will accept as DOUBLE PRECISION, the regex should be more like '^[-\+]?([0-9]+.?[0-9]*|[0-9]*.?[0-9]+)([Ee][-+]?[0-9]+)?$'.

But even this does not take into consideration that Vertica also accepts hexadecimal numbers, infinity, NAN and null values.