Home » SQL & PL/SQL » SQL & PL/SQL » Spell out numbers to words
Spell out numbers to words [message #38043] |
Thu, 14 March 2002 22:28 |
Cyrille PETIT
Messages: 5 Registered: March 2002
|
Junior Member |
|
|
I found a message which explain how to spell out numbers to words. I'm French and the purpose of my question is how to convert numbers to word (to print cheque) but in French the function found in the newsgroup was :
select decode( sign( &num ), -1, 'Negative ', 0, 'Zero', NULL ) ||
decode( sign( abs(&num) ), +1, to_char( to_date( abs(&num),'J'),'Jsp') )
from dual
/
I'm afraid but I don't understand how to pass my number to this function (&num ???)
TIA
Cyrille PETIT
|
|
|
Re: spell out numbers to words [message #38045 is a reply to message #38043] |
Thu, 14 March 2002 23:45 |
pratap kumar tripathy
Messages: 660 Registered: January 2002
|
Senior Member |
|
|
1.As u will need this often u better create a function
create or replace function spellnumber(v_num number)
return varchar2 as
v_word varchar2(500);
begin
select decode( sign( v_num ), -1, 'Negative ', 0, 'Zero', NULL ) ||
decode( sign( abs(v_num) ), +1, to_char( to_date( abs(v_num),'J'),'Jsp') )
into v_word from dual;
return v_word;
end;
/
2. then u can simply call the function as
t:=spellnumber(777);
|
|
|
|
|
|
Re: spell out numbers to words [message #349456 is a reply to message #38043] |
Sun, 21 September 2008 23:06 |
|
FUNCTION number_CONVERSION(NUM number) RETURN VARCHAR2
IS
A VARCHAR2(1000);
B VARCHAR2(20);
X number;
Y number := 1;
O VARCHAR2(200):='ONLY';
Z number;
V NUMBER;
LSIGN number;
NO number;
BEGin
X:= inSTR(NUM, '.');
LSIGN := SIGN(NUM);
NO := ABS(NUM);
IF X = 0 THEN
SELECT TO_CHAR(TO_DATE(NO, 'J'), 'JSP') inTO A FROM DUAL;
ELSE
SELECT to_char(to_date(SUBSTR(NO, 1,
NVL(inSTR(NO, '.')-1, LENGTH(NO))),
'J'), 'JSP') inTO A FROM DUAL;
SELECT LENGTH(SUBSTR(NO, inSTR(NO, '.')+2)) inTO Z FROM DUAL;
A := A||' '||'RUPEES'||' '||'&'||' '||'PAISAS ';
WHILE Y< Z+1 LOOP
SELECT TO_CHAR(TO_DATE(SUBSTR(NO, (inSTR(NO, '.')+Y),2), 'J'), 'JSP')
inTO B FROM DUAL;
A := A ||B||' ';
y :=y+1;
END LOOP;
END IF;
IF LSIGN = -1 THEN
RETURN 'NEGATIVE '||A||' '||O;
ELSE
RETURN A||' '||O;
END IF;
END;
________________________________________________________________
Thanks
Best, Regards
R I z w a n A d m a n iI.T ADMINISTRATOR
(OCP Developer
DBA Administrator
CCNA, CCDA, Network Administrator)
Contact : Rizwanadmani@gmail.com
|
|
|
Re: spell out numbers to words [message #349511 is a reply to message #38043] |
Mon, 22 September 2008 02:15 |
|
Alessandro Rossi
Messages: 166 Registered: September 2008 Location: Rome
|
Senior Member |
|
|
I use this one when I need it.
CREATE OR REPLACE
FUNCTION convert_number2word(
INP IN NUMBER
)
RETURN VARCHAR2
IS
X NUMBER;
Y VARCHAR2(32767);
DOT INTEGER;
VAL NUMBER;
FUNCTION spell (
X INTEGER
) RETURN VARCHAR2
IS
type STRING_TAB is table of varchar2(32);
OUT VARCHAR(32767);
SUFFIX STRING_TAB := STRING_TAB(
'',
'THOUSAND',
'MILLION',
'BILLION',
'TRILLION',
'QUADRILLION',
'QUINTILLION',
'SEXTILLION',
'SEPTILLION',
'OCTILLION',
'NONILLION',
'DECILLION',
'UNDECILLION',
'DUODECILLION'
);
VAL INTEGER := ABS(X);
CURR INTEGER;
OUT_STRING VARCHAR2(32767);
I INTEGER := 0;
BEGIN
LOOP
I:= I+1;
CURR := VAL MOD 1000;
VAL := VAL/1000;
OUT_STRING := TO_CHAR(TO_DATE(CURR,'J'),'JSP') ||' '|| SUFFIX(I) ||' '||OUT_STRING ;
EXIT WHEN ( VAL = 0 );
END LOOP;
RETURN OUT_STRING;
END;
BEGIN
X := INP;
VAL := abs(TRUNC(X));
if ( X < 0 ) then
y := 'MINUS ';
end if;
IF ( VAL = 0 ) THEN
Y := 'ZERO';
ELSE
Y:= Y || SPELL(VAL);
END IF;
IF ( abs(X - VAL) > 0 ) THEN
Y := Y ||' DOT ';
VAL := ABS(X) - VAL;
LOOP
EXIT WHEN VAL >= 0.1;
VAL := VAL * 10;
Y := Y ||' ZERO ';
END LOOP;
LOOP
EXIT WHEN VAL = TRUNC(VAL);
VAL := VAL * 10;
END LOOP;
Y := Y || SPELL(VAL);
END IF;
RETURN Y;
END;
/
Processing ...
select convert_number2word(-55646534.78698589567)
from dual
Query finished, retrieving results...
CONVERT_NUMBER2WORD(-55646534.78698589567)
--------------------------------------------------------------------------------
MINUS FIFTY-SIX MILLION SIX HUNDRED FORTY-SEVEN THOUSAND FIVE HUNDRED THIRTY-FOU-
R DOT SEVENTY-NINE BILLION SIX HUNDRED NINETY-NINE MILLION FIVE HUNDRED NINETY-
THOUSAND FIVE HUNDRED SIXTY-SEVEN
1 row(s) retrieved
Bye Alessandro
[Updated on: Mon, 22 September 2008 02:19] Report message to a moderator
|
|
|
|
|
Re: spell out numbers to words [message #500793 is a reply to message #500725] |
Wed, 23 March 2011 10:33 |
|
Alessandro Rossi
Messages: 166 Registered: September 2008 Location: Rome
|
Senior Member |
|
|
Sorry it, but it was not so good!!
You may try this other one.
CREATE FUNCTION convert_number2word(
INP IN NUMBER
)
RETURN VARCHAR2
IS
X NUMBER;
Y VARCHAR2(32767);
DOT INTEGER;
VAL NUMBER;
I INTEGER;
type STRING_TAB is table of varchar2(32);
SUFFIX STRING_TAB := STRING_TAB(
'',
'THOUSAND',
'MILLION',
'BILLION',
'TRILLION',
'QUADRILLION',
'QUINTILLION',
'SEXTILLION',
'SEPTILLION',
'OCTILLION',
'NONILLION',
'DECILLION',
'UNDECILLION',
'DUODECILLION'
);
N_SUFFIX INTEGER := SUFFIX.COUNT;
FUNCTION spell (
IN_X NUMBER,
IN_I INTEGER := 0
) RETURN VARCHAR2
IS
OUT VARCHAR(32767);
L_VAL NUMBER := ABS(IN_X);
CURR INTEGER;
OUT_STRING VARCHAR2(32767) := '';
BEGIN
IF ( L_VAL >= 1 ) THEN
LOOP
I:= I+1;
CURR := L_VAL MOD 1000;
L_VAL := TRUNC(L_VAL/1000);
IF (CURR > 0) THEN
OUT_STRING := TO_CHAR(TO_DATE(CURR,'J'),'JSP') || ' ' || SUFFIX(I) || ' ' ||OUT_STRING ;
END IF;
EXIT WHEN ( L_VAL = 0 );
END LOOP;
ELSIF L_VAL > 0 THEN
WHILE (I = 0 AND L_VAL < 0.1 ) LOOP
L_VAL := L_VAL * 10;
OUT_STRING := OUT_STRING || 'ZERO ';
END LOOP;
IF (L_VAL > 0) THEN
L_VAL := L_VAL * 1000;
CURR := TRUNC(L_VAL);
IF ( I=0 AND CURR = 0) THEN
RETURN SPELL(L_VAL);
ELSE
I := I + 1;
IF (CURR=0) THEN
OUT_STRING := OUT_STRING || SPELL(L_VAL-CURR);
ELSE
DECLARE
APP_STRING VARCHAR2(32767) := SPELL(L_VAL-CURR,IN_I+1);
BEGIN
OUT_STRING := OUT_STRING|| TO_CHAR(TO_DATE(CURR,'J'),'JSP') || ' ' || SUFFIX(I - IN_I) || ' ' || APP_STRING;
END;
END IF;
END IF;
END IF;
END IF;
RETURN OUT_STRING;
END;
BEGIN
X := INP;
I := 0;
VAL := abs(TRUNC(X));
if ( X < 0 ) then
y := 'MINUS ';
end if;
IF ( VAL = 0 ) THEN
Y := 'ZERO ';
ELSE
Y:= Y || SPELL(VAL);
END IF;
IF (VAL != ABS(X)) THEN
I := 0;
Y := Y || 'DOT ' || SPELL(abs(X - VAL));
END IF;
RETURN Y;
END;
/
Bye Alessandro
|
|
|
Re: spell out numbers to words [message #500968 is a reply to message #500793] |
Thu, 24 March 2011 13:06 |
|
sorry but it is wrong convert function
result see here
GETRS(55412.50)
---------------------------------------------------------------------
FIFTY-FIVE THOUSAND FOUR HUNDRED TWELVE DOT FIVE HUNDRED
|
|
|
Re: spell out numbers to words [message #500971 is a reply to message #500968] |
Thu, 24 March 2011 14:05 |
|
Alessandro Rossi
Messages: 166 Registered: September 2008 Location: Rome
|
Senior Member |
|
|
Saying DOT FIVE HUNDRED is the same as saying DOT FIVE anyway.
The approach to let it say DOT FIVE needs to analyze the entire number before begin printing the string.
This one may do that job.
CREATE OR REPLACE FUNCTION convert_number2word2(
INP IN NUMBER
)
RETURN VARCHAR2
IS
X NUMBER;
Y VARCHAR2(32767);
DOT INTEGER;
VAL NUMBER;
type STRING_TAB is table of varchar2(32);
SUFFIX STRING_TAB := STRING_TAB(
'',
'THOUSAND',
'MILLION',
'BILLION',
'TRILLION',
'QUADRILLION',
'QUINTILLION',
'SEXTILLION',
'SEPTILLION',
'OCTILLION',
'NONILLION',
'DECILLION',
'UNDECILLION',
'DUODECILLION'
);
N_SUFFIX INTEGER := SUFFIX.COUNT;
FUNCTION spell (
IN_X NUMBER
) RETURN VARCHAR2
IS
I INTEGER := 0;
L_VAL NUMBER := ABS(IN_X);
CURR INTEGER;
OUT_STRING VARCHAR2(32767) := '';
BEGIN
IF ( L_VAL >= 1 ) THEN
LOOP
I:= I+1;
CURR := L_VAL MOD 1000;
L_VAL := TRUNC(L_VAL/1000);
IF (CURR > 0) THEN
OUT_STRING := TO_CHAR(TO_DATE(CURR,'J'),'JSP') || ' ' || SUFFIX(I) || ' ' ||OUT_STRING ;
END IF;
EXIT WHEN ( L_VAL = 0 );
END LOOP;
ELSIF L_VAL > 0 THEN
WHILE (I = 0 AND L_VAL < 0.1 ) LOOP
L_VAL := L_VAL * 10;
OUT_STRING := OUT_STRING || 'ZERO ';
END LOOP;
WHILE (L_VAL > TRUNC(L_VAL)) LOOP
L_VAL := L_VAL * 10;
END LOOP;
OUT_STRING := OUT_STRING || ' ' ||SPELL(L_VAL);
END IF;
RETURN OUT_STRING;
END;
BEGIN
X := INP;
VAL := abs(TRUNC(X));
if ( X < 0 ) then
y := 'MINUS ';
end if;
IF ( VAL = 0 ) THEN
Y := 'ZERO ';
ELSE
Y:= Y || SPELL(VAL);
END IF;
IF (VAL != ABS(X)) THEN
Y := Y || 'DOT ' || SPELL(abs(X - VAL));
END IF;
RETURN Y;
END;
/
Bye Alessandro
|
|
|
|
Re: spell out numbers to words [message #669127 is a reply to message #669126] |
Wed, 04 April 2018 02:06 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
1/ The topic is 16 years old with a latest post 7 years ago, there are many newer functions (even for non-English languages)
2/ The function is only available for POSITIVE numbers.
3/ You are welcome to modify and post the function for negative numbers.
[Updated on: Wed, 04 April 2018 02:06] Report message to a moderator
|
|
|
Spell numbers (split) [message #669134 is a reply to message #38043] |
Wed, 04 April 2018 05:13 |
|
msol25
Messages: 396 Registered: June 2011
|
Senior Member |
|
|
Please use below logic:
with t as
( select -11234 a
from dual
)
select ( case when sign(a) < 0
then 'MINUS '
end
) ||
to_char( to_date(abs(a),'J'),'JSP')
from t;
Output:
MINUS ELEVEN THOUSAND TWO HUNDRED THIRTY-FOUR.
[Updated on: Wed, 04 April 2018 05:15] Report message to a moderator
|
|
|
Re: Spell numbers (split) [message #669142 is a reply to message #669134] |
Wed, 04 April 2018 15:13 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
J format is limited to 5373484:
SQL> with t as
2 ( select -5373485 a
3 from dual
4 )
5 select ( case when sign(a) < 0
6 then 'MINUS '
7 end
8 ) ||
9 to_char( to_date(abs(a),'J'),'JSP')
10 from t
11 /
to_char( to_date(abs(a),'J'),'JSP')
*
ERROR at line 9:
ORA-01854: julian date must be between 1 and 5373484
SQL>
It is better to use timestamp FF format with range between 0 and 999999999. However SP can't spell all numbers withing that range. SP buffer is limited so now it is length of spelled out number not it's denomination that limits us:
SQL> with t as
2 ( select -999999000 a
3 from dual
4 )
5 select ( case when sign(a) < 0
6 then 'MINUS '
7 end
8 ) ||
9 to_char(to_timestamp(to_char(abs(a)),'FF'),'FFSP')
10 from t
11 /
(CASEWHENSIGN(A)<0THEN'MINUS'END)||TO_CHAR(TO_TIMESTAMP(TO_CHAR(ABS(A)),'FF'),'F
--------------------------------------------------------------------------------
MINUS NINE HUNDRED NINETY-NINE MILLION NINE HUNDRED NINETY-NINE THOUSAND
SQL> with t as
2 ( select -333333333 a
3 from dual
4 )
5 select ( case when sign(a) < 0
6 then 'MINUS '
7 end
8 ) ||
9 to_char(to_timestamp(to_char(abs(a)),'FF'),'FFSP')
10 from t
11 /
to_char(to_timestamp(to_char(abs(a)),'FF'),'FFSP')
*
ERROR at line 9:
ORA-01877: string is too long for internal buffer
SQL>
SY.
|
|
|
Re: Spell numbers (split) [message #669165 is a reply to message #669142] |
Fri, 06 April 2018 05:46 |
Frank Naude
Messages: 4580 Registered: April 1998
|
Senior Member |
|
|
The ORA-01877 seems to be a SQL limit. The workaround is to use PL/SQL:
SQL> CREATE OR REPLACE FUNCTION spell_number(p_num NUMBER) RETURN VARCHAR2 IS
2 v_str VARCHAR2(200);
3 BEGIN
4 IF sign(p_num) < 0 THEN
5 v_str := 'MINUS ';
6 END IF;
7 RETURN v_str || TO_CHAR(TO_TIMESTAMP(TO_CHAR(ABS(p_num)),'FF'),'FFSP');
8 END;
9 /
Function created.
SQL> SELECT spell_number(-333333333) FROM dual;
SPELL_NUMBER(-333333333)
--------------------------------------------------------------------------------
MINUS THREE HUNDRED THIRTY-THREE MILLION THREE HUNDRED THIRTY-THREE THOUSAND THR
EE HUNDRED THIRTY-THREE
|
|
|
Re: Spell numbers (split) [message #669170 is a reply to message #669165] |
Fri, 06 April 2018 12:12 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Interesting. Thanks Frank!
Also, I messed up and it should be TO_CHAR(ABS(p_num),'000000000'), otherwise:
SQL> SELECT TO_CHAR(TO_TIMESTAMP(TO_CHAR(1),'FF'),'FFSP') WRONG,
2 TO_CHAR(TO_TIMESTAMP(TO_CHAR(1,'000000000'),'FF'),'FFSP') RIGHT
3 FROM DUAL
4 /
WRONG RIGHT
------------------------------ ------------------------------
ONE HUNDRED MILLION ONE
SQL>
SY.
|
|
|
Goto Forum:
Current Time: Sat Sep 28 15:40:31 CDT 2024
|