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 Go to next message
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 Go to previous messageGo to next message
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 #38048 is a reply to message #38043] Fri, 15 March 2002 02:40 Go to previous messageGo to next message
Cyrille PETIT
Messages: 5
Registered: March 2002
Junior Member
Thanks a lot for the response it's work fine.

Juste a "little" problem, I'm french as the database and I need to convert the number in french...

TIA

Cyrille PETIT
Re: spell out numbers to words [message #39682 is a reply to message #38043] Thu, 01 August 2002 20:24 Go to previous messageGo to next message
Muhammad Asif
Messages: 3
Registered: August 2002
Junior Member
How a number can be speeled.
e.g 1000 As one thousand.
Re: spell out numbers to words [message #39695 is a reply to message #38043] Fri, 02 August 2002 07:49 Go to previous messageGo to next message
Rick Cale
Messages: 111
Registered: February 2002
Senior Member
Try

SELECT TO_CHAR(TO_DATE(1000, 'J'), 'JSP') FROM DUAL;

TO_CHAR(TO_D
------------
ONE THOUSAND
Re: spell out numbers to words [message #349456 is a reply to message #38043] Sun, 21 September 2008 23:06 Go to previous messageGo to next message
ka_wish
Messages: 86
Registered: October 2007
Location: karachi
Member

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 Go to previous messageGo to next message
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 #349516 is a reply to message #349511] Mon, 22 September 2008 02:25 Go to previous messageGo to next message
Michel Cadot
Messages: 66808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ask Tom, Spell the number

Regards
Michel
Re: spell out numbers to words [message #500725 is a reply to message #349516] Wed, 23 March 2011 02:52 Go to previous messageGo to next message
georges.choueiry
Messages: 24
Registered: July 2008
Junior Member
there is an error in the code if you try

select convert_number2word(653793485000) from dual;
Re: spell out numbers to words [message #500793 is a reply to message #500725] Wed, 23 March 2011 10:33 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ka_wish
Messages: 86
Registered: October 2007
Location: karachi
Member

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 Go to previous messageGo to next message
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 #669126 is a reply to message #500971] Wed, 04 April 2018 00:52 Go to previous messageGo to next message
OCP_FAISAL
Messages: 1
Registered: April 2018
Junior Member
it is wrong convert function
result see here

SELECT convert_number2word(-55412.33) FROM DUAL;

Result : MINUS FIFTY-FIVE THOUSAND FOUR HUNDRED TWELVE DOT ONE HUNDRED TEN THOUSAND EIGHT HUNDRED TWENTY-FOUR
Re: spell out numbers to words [message #669127 is a reply to message #669126] Wed, 04 April 2018 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 66808
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2910
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 Go to previous messageGo to next message
Frank Naude
Messages: 4564
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 Go to previous message
Solomon Yakobson
Messages: 2910
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.
Previous Topic: Answer the query
Next Topic: Oracle 12C error tracing and debugging issue (merged 2)
Goto Forum:
  


Current Time: Wed Jan 29 02:27:11 CST 2020