"isdecimal(input_expression, decimal_format)" function in vertica

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)

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

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;

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]

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)

Hi,

Thank you. case Resolved