Uncategorized

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s