Split the number into groups of thousands and just process each of those:
Oracle Setup:
CREATE OR REPLACE FUNCTION TO_WORDS(
in_value IN INT
) RETURN VARCHAR2 DETERMINISTIC
AS
p_value INT := ABS( in_value );
p_words VARCHAR2(4000);
t_array CONSTANT SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(
null,
' thousand ',
' million ',
' billion ',
' trillion ',
' quadrillion ',
' quintillion ',
' sextillion ',
' septillion ',
' octillion ',
' decillion ',
' undecillion ',
' duodecillion '
);
p_counter INT := 0;
BEGIN
WHILE p_value > 0 LOOP
p_counter := p_counter + 1;
IF MOD( p_value, 1000 ) > 0 THEN
p_words := TO_CHAR( TO_DATE( MOD( p_value, 1000 ), 'j' ), 'jsp' ) || t_array( p_counter ) || p_words;
END IF;
p_value := FLOOR( p_value / 1000 );
END LOOP;
RETURN CASE WHEN in_value < 0 THEN RTRIM( 'minus ' || p_words )
WHEN in_value = 0 THEN 'Zero'
ELSE RTRIM( p_words ) END;
END;
/
Query:
SELECT TO_WORDS( 999999999999998 ) FROM DUAL;
Output:
TO_WORDS(999999999999998)
--------------------------------------------------------------------------------
nine hundred ninety-nine trillion nine hundred ninety-nine billion nine hundred
ninety-nine million nine hundred ninety-nine thousand nine hundred ninety-eight
Update - Using only SQL
(Note, this isn't simple)
SQL Fiddle
Oracle 11g R2 Schema Setup:
CREATE TABLE NUMBERS ( value ) AS
SELECT 123456789012345678901 FROM DUAL UNION ALL
SELECT 999000 FROM DUAL;
Query 1:
WITH words ( id, word ) AS (
SELECT 0, null FROM DUAL UNION ALL
SELECT 1, ' thousand' FROM DUAL UNION ALL
SELECT 2, ' million' FROM DUAL UNION ALL
SELECT 3, ' billion' FROM DUAL UNION ALL
SELECT 4, ' trillion' FROM DUAL UNION ALL
SELECT 5, ' quadrillion' FROM DUAL UNION ALL
SELECT 6, ' quintillion' FROM DUAL UNION ALL
SELECT 7, ' sextillion' FROM DUAL UNION ALL
SELECT 8, ' septillion' FROM DUAL UNION ALL
SELECT 9, ' octillion' FROM DUAL UNION ALL
SELECT 10, ' decillion' FROM DUAL UNION ALL
SELECT 11, ' undecillion' FROM DUAL UNION ALL
SELECT 12, ' duodecillion' FROM DUAL
)
SELECT MAX( n.value ) AS value,
LISTAGG(
CASE
WHEN MOD( FLOOR( n.value / POWER( 1000, t.column_value ) ), 1000 ) = 0
THEN NULL
ELSE TO_CHAR(
TO_DATE(
MOD( FLOOR( n.value / POWER( 1000, t.column_value ) ), 1000 ),
'j'
),
'jsp'
) || w.word
END,
' '
) WITHIN GROUP ( ORDER BY t.COLUMN_VALUE DESC )
AS to_words
FROM numbers n,
TABLE(
CAST(
MULTISET(
SELECT LEVEL - 1
FROM DUAL
CONNECT BY POWER( 1000, LEVEL - 1 ) < n.value
) AS SYS.ODCINUMBERLIST
)
) t,
words w
WHERE t.column_value = w.id
GROUP BY n.ROWID
Results:
| VALUE | TO_WORDS |
|-----------------------|--------------------------------------|
| 12345678901234568901 | one hundred twenty-three quintillion |
| | four hundred fifty-six quadrillion |
| | seven hundred eighty-nine trillion |
| | twelve billion |
| | three hundred forty-five million |
| | six hundred seventy-eight thousand |
| | nine hundred one |
| | |
| 999000 | nine hundred ninety-nine thousand |
An alternative SQL method is:
SELECT *
FROM table_name t
CROSS JOIN LATERAL (
SELECT LISTAGG(
CASE
WHEN depth = 1 AND part = '0'
THEN 'ZERO'
WHEN part = '000'
THEN NULL
ELSE CASE
WHEN is_last = 0
THEN ' '
END ||
TO_CHAR(TO_DATE(part, 'J'), 'JSP') ||
CASE depth
WHEN 1 THEN NULL
WHEN 2 THEN ' THOUSAND'
WHEN 3 THEN ' MILLION'
WHEN 4 THEN ' BILLION'
WHEN 5 THEN ' TRILLION'
WHEN 6 THEN ' QAUDRILLION'
WHEN 7 THEN ' QUINTILLION'
WHEN 8 THEN ' SEXTILLION'
WHEN 9 THEN ' SEPTILLION'
WHEN 10 THEN ' OCTILLION'
WHEN 11 THEN ' NONILLION'
WHEN 12 THEN ' DECILLION'
WHEN 13 THEN ' UNDECILLION'
WHEN 14 THEN ' DUODECILLION'
END
END,
NULL
) WITHIN GROUP (ORDER BY depth DESC) AS words
FROM (
SELECT CASE
WHEN 3*LEVEL > LENGTH(value)
THEN SUBSTR(value, 1, MOD(LENGTH(value), 3))
ELSE SUBSTR(value, -3*LEVEL, 3)
END AS part,
LEVEL AS depth,
CONNECT_BY_ISLEAF AS is_last
FROM DUAL
CONNECT BY 3 * LEVEL - 2 <= LENGTH(value)
)
);
fiddle
