Display a number value in Words

[Jbaskin]

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.

[JimKnicely]

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:

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;

Calling the UDF:

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)

You would need to have a WHEN clause for every possible value you need converted. That's a lot of work

[id10t]

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]

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]

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

[jpcavanaugh]

Is there any option for you to load this up to the Vertica github repo?

[id10t]

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

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)

PS
@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;

[becky]

sKwa, This program you wrote is awesome! THANKS!