"isdecimal(input_expression, decimal_format)" function in vertica

bambabamba Registered User

Hi support,

Is the "Isdecimal" function avaible under vertica. It's the same question for isdate() and isint()

Thanks

Comments

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

    You can create your own SQL functions....

    Simple Example:

    dbadmin=> create function isdecimal(x varchar) return varchar
    dbadmin-> as
    dbadmin-> begin
    dbadmin-> return REGEXP_COUNT(x, '^(\d*\.)?\d+$');
    dbadmin-> end;
    CREATE FUNCTION
    
    dbadmin=> select isdecimal('1.0');
     isdecimal
    -----------
     1
    (1 row)
    
    dbadmin=> select isdecimal('1');
     isdecimal
    -----------
     1
    (1 row)
    
    dbadmin=> select isdecimal('A');
     isdecimal
    -----------
     0
    (1 row)
    
    dbadmin=> select isdecimal('0.2345');
     isdecimal
    -----------
     1
    (1 row)
    
  • bambabamba Registered User

    Hi support,

    Thank you for your reply.
    I tested your SQL function and it works for positive numbers, by changing the REGEXP_COUNT it works also for negative numbers. However, I wanted to have this possibility to pass in parameter the format of the expected decimal. Can you explain to me how to implement this in vertica ? I want a SQL function that looks like this below.
    isdecimal(input_expression, decimal_format)
    Examples:
    isdecimal('-10,905', '- #.##') ==> return 0 because it does not match the expected format
    isdecimal('-10,905', '#.##') ==> return 0 because it does not match the expected format
    isdecimal('-10,905', '- #.###') ==> return 1 because it matches the expected format
    isdecima(‘2’) ==> return 1

    etc…
    Thank for your help

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
    edited November 2017

    I'm sure there are a million ways to create the function you want! Here's one way to get you started:

    create or replace function isdecimal(x varchar, y varchar) return boolean
    as
    begin
    return REGEXP_COUNT(x, '\.') = 1 and
           LENGTH(split_part(x, '.', 2)) = LENGTH(split_part(y, '.', 2)) and
           REGEXP_COUNT(split_part(x, '.', 1), '^(-?[1-9]+\d*)$|^0$') and
           REGEXP_COUNT(split_part(x, '.', 2), '^([1-9]+\d*)$|^0$');
    end;
    

    Here is the output of your samples:

    dbadmin=> select isdecimal('-10.905', '-#.##');
     isdecimal
    -----------
     f
    (1 row)
    
    dbadmin=> select isdecimal('-10.905', '#.##');
     isdecimal
    -----------
     f
    (1 row)
    
    dbadmin=> select isdecimal('-10.905', '-#.###');
     isdecimal
    -----------
     t
    (1 row)
    
    dbadmin=> select isdecimal('2');
    ERROR 3457:  Function isdecimal(unknown) does not exist, or permission is denied for isdecimal(unknown)
    HINT:  No function matches the given name and argument types. You may need to add explicit type casts
    

    For that last one, it failed 'cause no format string was passed. But is 2 a decimal? I think 2.0 is.

    dbadmin=> select isdecimal('2.0', '#.#');
     isdecimal
    -----------
     t
    (1 row)
    

    Anyway, you can modify the code to handle what ever case you want and also check the format string, i.e. does it only contain a negative sign and # characters?

    You can also go back to the original REGEX, I was just trying out the SPLIT_PART function...

    dbadmin=> create or replace function isdecimal(x varchar, y varchar) return boolean
    dbadmin-> as
    dbadmin-> begin
    dbadmin->   return REGEXP_COUNT(x, '^(-?\d*\.)?\d+$') and LENGTH(split_part(x, '.', 2)) = LENGTH(split_part(y, '.', 2));
    dbadmin-> end;
    
  • bambabamba Registered User

    Hi,

    Thank you for that answer.
    After several tests this method does not cover all cases.
    I wanted to proceed as follows:

    CREATE OR REPLACE FUNCTION "DMM1E"."test_date"(input_expression Varchar, date_format Varchar)
    RETURN varchar(20)
    AS
    BEGIN
    RETURN (CASE ((input_expression)::!date)::varchar WHEN NULLSEQUAL input_expression THEN 1 ELSE 0 END)::varchar(20);
    END;

    I wanted to store the result (input_expression) ::! date in a variable you can tell me how to use a variable in vertica function(SQL).
    When I call the function with test_date ('2017-02-29',) I get the error message below. I want to get 0. Thank you for your help.

    ... Physical database connection acquired for:
    13:47:55 [SELECT - 0 rows, 0.016 secs] [Code: 2992, SQL State: 22008] [Vertica]VJDBC ERROR: Date/time field value out of range: "2017-02-29"... 1 statement(s) executed, 0 rows affected, exec/fetch time: 0.016/0.000 sec [0 successful, 1 errors]

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
    edited November 2017

    Hi,

    User Defined SQL Functions have to be pretty simple. There is no way to store data in variables or handle the type of error you are receiving.

    Maybe the following function that uses yet another REGEXP can help?

    Note: The regular expression matches ANSI SQL date format YYYY-mm-dd hh:mi:ss am/pm. You can use / - or space for date delimiters, so 2017-12-31 works just as well as 2017/12/31. It also checks leap year from 1901 to 2099.

    dbadmin=> create or replace function isdate(x varchar) return varchar
    dbadmin-> as
    dbadmin-> begin
    dbadmin-> return REGEXP_COUNT(x, '^((\d{2}(([02468][048])|([13579][26]))[\-\/\s]?((((0?[13578])|(1[02]))[\-\/\s]?((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\-\/\s]?((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\-\/\s]?((0?[1-9])|([1-2][0-9])))))|(\d{2}(([02468][1235679])|([13579][01345789]))[\-\/\s]?((((0?[13578])|(1[02]))[\-\/\s]?((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\-\/\s]?((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\-\/\s]?((0?[1-9])|(1[0-9])|(2[0-8]))))))(\s(((0?[1-9])|(1[0-2]))\:([0-5][0-9])((\s)|(\:([0-5][0-9])\s))([AM|PM|am|pm]{2,2})))?$');
    dbadmin-> end;
    CREATE FUNCTION
    
    dbadmin=> select isdate('2017-11-21');
     isdate
    --------
     1
    (1 row)
    
    dbadmin=> select isdate('2017-02-29');
     isdate
    --------
     0
    (1 row)
    
    dbadmin=> select isdate('2020-02-29');
     isdate
    --------
     1
    (1 row)
    
  • bambabamba Registered User

    Hi,

    Thank you. case Resolved

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file