Sunday, 26 October 2014

isnumeric function




isnumeric function is not a built in function in Vertica, but we can create it.

CREATE FUNCTION ISNUMERIC(X VARCHAR) RETURN VARCHAR
AS
BEGIN
RETURN REGEXP_COUNT(X, '^[0-9.-]+$');
Posted by at

1 comment:

  1. Unknown29 January 2015 at 13:05

    This function will not accurately identify a numeric number.

    The following are some valid inputs for a double precision number in Vertica that the function above will mark as not numeric:
    +0.1
    1.89e-10

    The following are some inputs that the function would flag as numeric that Vertica will not accept:
    .00.0.0.0.
    -----

    Your regex should be more like
    '^[-\+]?([0-9]+.?[0-9]*|[0-9]*.?[0-9]+)([Ee][-+]?[0-9]+)?$'

    ReplyDelete

Subscribe to: Post Comments (Atom)