Home » SQL & PL/SQL » SQL & PL/SQL » spell out numbers to words  () 1 Vote
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 undedant how 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: 85
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 #349485 is a reply to message #349456] Mon, 22 September 2008 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
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: 64153
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: 85
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 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
Previous Topic: converting binary to ascii
Next Topic: Extract the correct value for child records
Goto Forum:
  


Current Time: Sun Dec 11 02:02:29 CST 2016

Total time taken to generate the page: 0.04649 seconds