Vertica SQL function Words_to_number

Hi There,

Just a quick update where we left last time - Vertica SQL Function

Here is a good example of Vertica SQL Function

The name of this function is words_to_number
This function takes arguments as a two word digit say 'seventy one' and gives the result in numbers .i.e 71

Just observe the way we can use the SQL function in Vertica to optimize the reusable codes.

Function Definition

CREATE OR REPLACE FUNCTION WORDS_TO_NUMBER(x VARCHAR) RETURN VARCHAR
AS BEGIN
RETURN
CASE
WHEN REGEXP_COUNT(SPLIT_PART(x, ' ', 1), 'ty') = 1 then
(CASE 
WHEN SPLIT_PART(lower(x), ' ', 1) = 'twenty' THEN 2*10 
WHEN SPLIT_PART(lower(x), ' ', 1) = 'thirty' THEN 3*10 
WHEN SPLIT_PART(lower(x), ' ', 1) = 'forty' THEN 4*10
WHEN SPLIT_PART(lower(x), ' ', 1) = 'fifty' THEN 5*10
WHEN SPLIT_PART(lower(x), ' ', 1) = 'sixty' THEN 6*10
WHEN SPLIT_PART(lower(x), ' ', 1) = 'seventy' THEN 7*10
WHEN SPLIT_PART(lower(x), ' ', 1) = 'eighty' THEN 8*10
WHEN SPLIT_PART(lower(x), ' ', 1) = 'ninety' THEN 9*10 ELSE '0' END)
ELSE '0' END +
CASE
WHEN REGEXP_COUNT(x, ' [a-z]') = 1 then
(CASE 
WHEN SPLIT_PART(lower(x), ' ', 2) = 'one' THEN 1*1
WHEN SPLIT_PART(lower(x), ' ', 2) = 'two' THEN 2*1
WHEN SPLIT_PART(lower(x), ' ', 2) = 'three' THEN 3*1
WHEN SPLIT_PART(lower(x), ' ', 2) = 'four' THEN 4*1
WHEN SPLIT_PART(lower(x), ' ', 2) = 'five' THEN 5*1
WHEN SPLIT_PART(lower(x), ' ', 2) = 'six' THEN 6*1
WHEN SPLIT_PART(lower(x), ' ', 2) = 'seven' THEN 7*1
WHEN SPLIT_PART(lower(x), ' ', 2) = 'eight' THEN 8*1
WHEN SPLIT_PART(lower(x), ' ', 2) = 'nine' THEN 9*1
ELSE '0' END)
WHEN REGEXP_COUNT(x,' ') = 0 THEN
(CASE
WHEN lower(x) = 'one' THEN 1
WHEN lower(x) = 'two' THEN 2
WHEN lower(x) = 'three' THEN 3
WHEN lower(x) = 'four' THEN 4
WHEN lower(x) = 'five' THEN 5
WHEN lower(x) = 'six' THEN 6
WHEN lower(x) = 'seven' THEN 7
WHEN lower(x) = 'eight' THEN 8
WHEN lower(x) = 'nine' THEN 9
ELSE '0' END) 
ELSE 0 END;

END;

Always remember to use BEGIN and END statements as in Function definition.
And the most important or you can say limit to Vertica SQL Function is no usage of Select statement is allowed in Function statement.

Some example to use this function
Function Usage

nnani=> select WORDS_TO_NUMBER('twenty three');

 WORDS_TO_NUMBER
-----------------
 23
(1 row)

You can also try some more example with this function

select WORDS_TO_NUMBER('ninety nine');

select WORDS_TO_NUMBER('seventy one');

select WORDS_TO_NUMBER('one');

The rules / limits when using this functions are the words can only extend till two digits, you cannot have a three word digit in the arguments.

Go ahead and use this function. Let me know your reviews on this.