"isdecimal(input_expression, decimal_format)" function in vertica

Comments

• Jim_Knicely - Select Field - Administrator

  November 2017

  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)
  

  1
• bamba

  November 2017

  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

  0
• Jim_Knicely - Select Field - Administrator

  November 2017 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;
  

  0
• bamba

  November 2017

  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]

  

  

  

  

  0
• Jim_Knicely - Select Field - Administrator

  November 2017 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)
  

  0
• bamba

  November 2017

  Hi,

  Thank you. case Resolved

  0