Oracle PL/SQL: Convert number to words without using to_date

Question

Is there a simple alternative to using to_date function to convert numbers to words in Oracle using PL/SQL like 0 -> Zero, 1->One, 2->Two etc

I researched a lot and found the below popular statement but I have a requirement to do the same without using to_date() for e.g.

SELECT TO_CHAR(TO_DATE(500,'J'),'Jsp')) FROM dual;

to_date is to convert date literals to a DATE type. What does that have to do with "words?" Not clear. — OldProgrammer, Mar 25 '18 at 23:59
@OldProgrammer take a look here: https://stackoverflow.com/questions/16123413/how-to-display-number-value-in-words — Ekta Sharma, Mar 26 '18 at 00:06
*Is there a "simpler" solution than using the only built-in solution* = No. The to_char() parameter `'Jsp'` requires Julian dates, and once you have Julian dates then it can "sp"ell them for you. To get Julian dates you need the to_date() function. — Paul Maxwell, Mar 26 '18 at 00:33
The accepted answer of the possible duplicate uses `TO_DATE` to perform the conversion. OP specifically states they don't want to use `TO_DATE`, so my evaluation is that this is not a dup. — Bob Jarvis - Слава Україні, Mar 26 '18 at 02:18
It seems that people have got a homework; we've discussed exactly the same question a few days ago, so - yes, I' say that this is a duplicate. — Littlefoot, Mar 26 '18 at 06:09

score 0 · Answer 1 · answered Mar 26 '18 at 06:32

Why is there a requirement not to use TO_DATE ? That seems an odd requirement. It's like saying "I want to query data but not use SELECT".

If, in fact, the issue is that you want to be able to exceed the values allowed using the TO_DATE approach, you can extend it easily enough

SQL> create or replace
  2  function spell_number( p_number in number )
  3  return varchar2
  4  as
  5  type myArray is table of varchar2(255);
  6  l_str myArray := myArray( '',
  7  ' thousand ', ' million ',
  8  ' billion ', ' trillion ',
  9  ' quadrillion ', ' quintillion ',
 10   ' sextillion ', ' septillion ',
 11   ' octillion ', ' nonillion ',
 12   ' decillion ', ' undecillion ',
 13   ' duodecillion ' );
 14
 15   l_num varchar2(50) default trunc( p_number );
 16   l_return varchar2(4000);
 17   begin
 18   for i in 1 .. l_str.count
 19   loop
 20   exit when l_num is null;
 21
 22   if ( substr(l_num, length(l_num)-2, 3) <> 0 )
 23   then
 24   l_return := to_char(
 25   to_date(
 26   substr(l_num, length(l_num)-2, 3),
 27   'J' ),
 28   'Jsp' ) || l_str(i) || l_return;
 29   end if;
 30   l_num := substr( l_num, 1, length(l_num)-3 );
 31   end loop;
 32
 33   return l_return;
 34   end;
 35  /

Function created.

SQL>
SQL> select spell_number( 12345678901234567890123456789012345678 ) from dual;
Twelve undecillion Three Hundred Forty-Five decillion Six Hundred Seventy-Eight nonillion Nine Hundr
ed One octillion Two Hundred Thirty-Four septillion Five Hundred Sixty-Seven sextillion Eight Hundre
d Ninety quintillion One Hundred Twenty-Three quadrillion Four Hundred Fifty-Six trillion Seven Hund
red Eighty-Nine billion Twelve million Three Hundred Forty-Five thousand Six Hundred Seventy-Eight

Oracle PL/SQL: Convert number to words without using to_date

1 Answers1