Home » SQL & PL/SQL » SQL & PL/SQL » Number to words (pl sql)
| Number to words [message #652336] |
Tue, 07 June 2016 09:50  |
 |
raghuramgupta
Messages: 1
Registered: June 2016
Location: singapore
|
Junior Member |
|
|
Hi,
I'm newbie to pl sql, I need to convert numbers to words
1 to First
2 to Seconds
3 to Third
4 to Fourth etc.......
Can suggestions?
|
|
|
|
| Re: Number to words [message #652337 is a reply to message #652336] |
Tue, 07 June 2016 09:54  |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
THE FOLLOWING FUNCTION WILL DO THE TRICK.
CREATE OR REPLACE FUNCTION Spell_number (P_number IN NUMBER, P_format IN VARCHAR2)
RETURN VARCHAR2
IS
TYPE Myarray IS TABLE OF VARCHAR2 (255);
L_str Myarray
:= Myarray ('',
' thousand ',
' million ',
' billion ',
' trillion ',
' quadrillion ',
' quintillion ',
' sextillion ',
' septillion ',
' octillion ',
' nonillion ',
' decillion ',
' undecillion ',
' duodecillion ');
L_num VARCHAR2 (50) DEFAULT TRUNC (P_number);
L_return VARCHAR2 (4000);
X_point VARCHAR2 (20);
BEGIN
IF UPPER (P_format) = 'D'
THEN
X_point := ' Dollars and';
ELSE
X_point := ' point';
END IF;
FOR I IN 1 .. L_str.COUNT
LOOP
EXIT WHEN L_num IS NULL;
IF (SUBSTR (L_num, LENGTH (L_num) - 2, 3) <> 0)
THEN
L_return :=
TO_CHAR (
TO_DATE (SUBSTR (L_num, LENGTH (L_num) - 2, 3), 'J'),
'Jsp')
|| L_str (I)
|| L_return;
END IF;
L_num := SUBSTR (L_num, 1, LENGTH (L_num) - 3);
END LOOP;
-- beginning of section added to include decimal places:
IF TO_CHAR (P_number) LIKE '%.%'
THEN
L_num := SUBSTR (P_number, INSTR (P_number, '.') + 1);
IF P_format = 'D'
THEN
IF L_num > 99
THEN
RETURN NULL;
ELSIF LENGTH (L_num) < 2
THEN
L_num := L_num || '0';
END IF;
L_return := L_return || ' Dollars and ' || L_num || ' Cents';
RETURN L_return;
END IF;
IF L_num > 0
THEN
L_return := L_return || X_point;
FOR I IN 1 .. LENGTH (L_num)
LOOP
EXIT WHEN L_num IS NULL;
IF SUBSTR (L_num, 1, 1) = '0'
THEN
L_return := L_return || ' zero';
ELSE
L_return :=
L_return
|| ' '
|| TO_CHAR (TO_DATE (SUBSTR (L_num, 1, 1), 'j'), 'jsp');
END IF;
L_num := SUBSTR (L_num, 2);
END LOOP;
END IF;
ELSE
IF P_format = 'D'
THEN
L_return := L_return || ' Dollars and 00 Cents';
END IF;
END IF;
-- end of section added to include decimal places
RETURN L_return;
END Spell_number;
/ |
|
|
|
| Re: Number to words [message #652340 is a reply to message #652336] |
Tue, 07 June 2016 10:20 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Or if your number is less or equal 5373484:
SQL> select level, to_char(to_date(level,'J'),'Jspth') spelled from dual connect by level <= 10;
LEVEL SPELLED
---------- --------------------------------------------------------------------------------
1 First
2 Second
3 Third
4 Fourth
5 Fifth
6 Sixth
7 Seventh
8 Eighth
9 Ninth
10 Tenth
SQL> select to_char(to_date(5373484,'J'),'Jspth') "5373484" from dual;
5373484
----------------------------------------------------------------------------
Five Million Three Hundred Seventy-Three Thousand Four Hundred Eighty-Fourth
SQL> select to_char(to_date(5373485,'J'),'Jspth') "5373484" from dual;
select to_char(to_date(5373485,'J'),'Jspth') "5373484" from dual
*
ERROR at line 1:
ORA-01854: julian date must be between 1 and 5373484
|
|
|
|
| Re: Number to words [message #652342 is a reply to message #652336] |
Tue, 07 June 2016 10:23 |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
How high do you need to go?
If you don't need to go higher than 31, you can get away with this:
scott@XE_11g> SELECT TO_CHAR(TO_DATE(31, 'J'),'ddspth') x from dual;
X
------------
thirty-first
scott@XE_11g>
If you need to go higher than that, you have to get a little more creative:
scott@XE_11g> VAR n NUMBER
scott@XE_11g> EXEC :n := 54321;
PL/SQL procedure successfully completed.
scott@XE_11g> SELECT TO_CHAR(TO_TIMESTAMP('20000101000000' || LPAD(TO_CHAR(:n), 9, '0')
2 , 'YYYYMMDDHH24MISSFF9')
3 , 'ffspth') x
4 FROM dual;
X
--------------------------------------------------------------------------------
fifty-four thousand three hundred twenty-first
scott@XE_11g> EXEC :n := 999999001;
PL/SQL procedure successfully completed.
scott@XE_11g> /
X
--------------------------------------------------------------------------------
nine hundred ninety-nine million nine hundred ninety-nine thousand first
scott@XE_11g>
As for the limits of the values this function can accept, Oracle's routine starts behaving strangely (for me) above the 999,999,000 mark. For example, it works fine for the most part, but fails at 999,999,027 (?), succeeds, fails intermittently after that, raising ORA-01877's. To test on your system, you can borrow my little quick-and-dirty test harness:
DECLARE
x LONG;
BEGIN
FOR i IN 999999000..999999305
LOOP
BEGIN
SELECT TO_CHAR(TO_TIMESTAMP('20000101000000' || LPAD(TO_CHAR(i), 9, '0')
, 'YYYYMMDDHH24MISSFF9')
, 'ffspth') x2
INTO x
FROM dual;
DBMS_OUTPUT.PUT_LINE(SUBSTR(TO_CHAR(i), -3) || ': ok');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(TO_CHAR(i),-3) || ': not ok');
END;
END LOOP;
END;
/
Note, this formatting of ordinal numbers of Oracle's only works for the English language.
|
|
|
|
| Re: Number to words [message #652344 is a reply to message #652342] |
Tue, 07 June 2016 10:39 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Nice, didn't though about using subsecond timestamp part which enlarge the 5373484 DATE limit:
SQL> select level,
2 to_char(cast(trunc(sysdate) as timestamp)+numtodsinterval(level/1000000000,'second'),'ffspth') spelled
3 from dual
4 connect by level <= 10
5 /
LEVEL SPELLED
---------- --------------------------------------------------------------------------------
1 first
2 second
3 third
4 fourth
5 fifth
6 sixth
7 seventh
8 eighth
9 ninth
10 tenth
SQL> select level+5373480,
2 to_char(cast(trunc(sysdate) as timestamp)+numtodsinterval((level+5373480)/1000000000,'second'),'ffspth') spelled
3 from dual
4 connect by level <= 10
5 /
LEVEL+5373480 SPELLED
------------- --------------------------------------------------------------------------------
5373481 five million three hundred seventy-three thousand four hundred eighty-first
5373482 five million three hundred seventy-three thousand four hundred eighty-second
5373483 five million three hundred seventy-three thousand four hundred eighty-third
5373484 five million three hundred seventy-three thousand four hundred eighty-fourth
5373485 five million three hundred seventy-three thousand four hundred eighty-fifth
5373486 five million three hundred seventy-three thousand four hundred eighty-sixth
5373487 five million three hundred seventy-three thousand four hundred eighty-seventh
5373488 five million three hundred seventy-three thousand four hundred eighty-eighth
5373489 five million three hundred seventy-three thousand four hundred eighty-ninth
5373490 five million three hundred seventy-three thousand four hundred ninetieth
SQL> select to_char(cast(trunc(sysdate) as timestamp)+numtodsinterval(&N/1000000000,'second'),'ffspth') spelled from dual;
Enter value for n: 999999000
SPELLED
--------------------------------------------------------------------------------
nine hundred ninety-nine million nine hundred ninety-nine thousandth
SQL> /
Enter value for n: 999999001
SPELLED
--------------------------------------------------------------------------------
nine hundred ninety-nine million nine hundred ninety-nine thousand first
SQL> /
Enter value for n: 999999010
SPELLED
--------------------------------------------------------------------------------
nine hundred ninety-nine million nine hundred ninety-nine thousand tenth
SQL> /
Enter value for n: 999999090
SPELLED
--------------------------------------------------------------------------------
nine hundred ninety-nine million nine hundred ninety-nine thousand ninetieth
SQL> /
Enter value for n: 999999100
SPELLED
--------------------------------------------------------------------------------
nine hundred ninety-nine million nine hundred ninety-nine thousand one hundredth
SQL> /
Enter value for n: 999999999
select to_char(cast(trunc(sysdate) as timestamp)+numtodsinterval(999999999/1000000000,'second'),'ffspth') spelled from dual
*
ERROR at line 1:
ORA-01877: string is too long for internal buffer
[Updated on: Tue, 07 June 2016 10:45] Report message to a moderator |
|
|
|
| Re: Number to words [message #652348 is a reply to message #652344] |
Tue, 07 June 2016 11:35 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
Using the function I have above
test>select spell_number(123456789012.9876,'P') from dual;
SPELL_NUMBER(123456789012.9876,'P')
--------------------------------------------------------------------------------
One Hundred Twenty-Three billion Four Hundred Fifty-Six million Seven Hundred Ei
ghty-Nine thousand Twelve point nine eight seven six
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 00:49:35 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
