Display a number value in Words
Moderator: NorbertKrupa
Display a number value in Words
Good morning,
Is there a way that I can display a number in words in Vertica using SQL?
Examples...
10 as Ten
800 as Eight Hundred
1700 as One Thousand Seven Hundred
1250 as One Thousand Two Hundred Fifty
Thank you.
Is there a way that I can display a number in words in Vertica using SQL?
Examples...
10 as Ten
800 as Eight Hundred
1700 as One Thousand Seven Hundred
1250 as One Thousand Two Hundred Fifty
Thank you.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Display a number value in Words
Hi there,
Hmm. You may have to write an C++ program as an external procedure for this requirement in Vertica. I know Oracle has a trick to do this using Julian dates, but it doesn't work in Vertica.
Just a thought... if you have a relatively small range of numbers in which you need to display as words, maybe you could create a UDF with one gigantic CASE statement.
Here's a small example.
Code for the UDF:
Calling the UDF:
You would need to have a WHEN clause for every possible value you need converted. That's a lot of work
Hmm. You may have to write an C++ program as an external procedure for this requirement in Vertica. I know Oracle has a trick to do this using Julian dates, but it doesn't work in Vertica.
Just a thought... if you have a relatively small range of numbers in which you need to display as words, maybe you could create a UDF with one gigantic CASE statement.
Here's a small example.
Code for the UDF:
Code: Select all
CREATE FUNCTION spell_out_num (x int) return varchar
AS
BEGIN
RETURN (CASE x
WHEN 1 THEN 'ONE'
WHEN 2 THEN 'TWO'
WHEN 3 THEN 'THREE'
WHEN 4 THEN 'FOUR'
WHEN 5 THEN 'FIVE'
WHEN 6 THEN 'SIX'
WHEN 7 THEN 'SEVEN'
WHEN 8 THEN 'EIGHT'
WHEN 9 THEN 'NINE'
WHEN 10 THEN 'TEN'
WHEN 800 THEN 'EIGHT HUNDRED'
WHEN 1250 THEN 'ONE THOUSAND TWO HUNDRED FIFTY'
WHEN 1700 THEN 'ONE THOUSAND SEVEN HUNDRED'
ELSE 'OUT OF RANGE'
END);
END;
Code: Select all
dbadmin=> SELECT spell_out_num (1);
spell_out_num
---------------
ONE
(1 row)
Code: Select all
dbadmin=> SELECT spell_out_num (10);
spell_out_num
---------------
TEN
(1 row)
Code: Select all
dbadmin=> SELECT spell_out_num (1250);
spell_out_num
--------------------------------
ONE THOUSAND TWO HUNDRED FIFTY
(1 row)
Code: Select all
dbadmin=> SELECT spell_out_num (1700);
spell_out_num
----------------------------
ONE THOUSAND SEVEN HUNDRED
(1 row)
Code: Select all
dbadmin=> SELECT spell_out_num (1000000);
spell_out_num
---------------
OUT OF RANGE
(1 row)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Display a number value in Words
Hi!
[Edit by Forums Admin: Please refer to the post viewtopic.php?f=48&t=349#p1201 by sKwa for a valid link to his code]
[Edit by Forums Admin: Please refer to the post viewtopic.php?f=48&t=349#p1201 by sKwa for a valid link to his code]
Code: Select all
dbadmin=> select numbering(3011);
numbering
-------------------------
three thousand eleven
(1 row)
Code: Select all
dbadmin=> select numbering(999);
numbering
----------------------------
nine hundred ninety nine
(1 row)
Code: Select all
dbadmin=> select numbering(567890321);
numbering
-----------------------------------------------------------------------------------------------
five hundred sixty seven million eight hundred ninety thousand three hundred twenty one
(1 row)
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Display a number value in Words
sKwa,
Great work! That'll help a lot of folks out there!
Your solution is much better than creating a giant CASE statement having 1 billion WHEN clauses in a UDF
Great work! That'll help a lot of folks out there!
Your solution is much better than creating a giant CASE statement having 1 billion WHEN clauses in a UDF
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
-
- Intermediate
- Posts: 149
- Joined: Mon Apr 30, 2012 10:04 pm
- Location: New York
- Contact:
Re: Display a number value in Words
Is there any option for you to load this up to the Vertica github repo?
Re: Display a number value in Words
Hi!
1. I updated my UDF function - now it's name SpellOutNumber.cpp --- http://github.com/sKwa/vertica/blob/mas ... Number.cpp
2. Function limited to number less than 1 billion
PS
@juniorfoo - its not depend on me. I saw user `alamb` registered on forum, if he will want he will fork my function.
PSS
1. I updated my UDF function - now it's name SpellOutNumber.cpp --- http://github.com/sKwa/vertica/blob/mas ... Number.cpp
2. Function limited to number less than 1 billion
Code: Select all
dbadmin=> select spell_number(1001);
spell_number
------------------
one thousand one
(1 row)
Code: Select all
dbadmin=> select spell_number(999);
spell_number
--------------------------
nine hundred ninety-nine
(1 row)
Code: Select all
dbadmin=> select spell_number(32601007);
spell_number
---------------------------------------------------
thirty-two million six hundred one thousand seven
(1 row)
@juniorfoo - its not depend on me. I saw user `alamb` registered on forum, if he will want he will fork my function.
PSS
- Compile:
Code: Select all
g++ -D HAVE_LONG_INT_64 -I /opt/vertica/sdk/include \ -Wall -shared -Wno-unused-value -fPIC \ -o NumberingUDF.so SpellOutNumber.cpp /opt/vertica/sdk/include/Vertica.cpp
- Installation (vsql):
Code: Select all
CREATE LIBRARY NumberingUDF AS <path to shared library>; CREATE FUNCTION spell_number AS LANGUAGE 'C++' NAME 'SpellOutNumberFactory' LIBRARY NumberingUDF;
Re: Display a number value in Words
sKwa, This program you wrote is awesome! THANKS!
THANKS - BECKSTER