Home » SQL & PL/SQL » SQL & PL/SQL » Convertion from Number to Word
Convertion from Number to Word [message #259180] Tue, 14 August 2007 10:36 Go to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

Attached file contains a function which converts the number to Word.
Quote:
/* Name : TO_WORD *
* Athor : Sanka Yanka *
* Purpose : To convert number to word format *
* Parameters : nNumber (Accept Number as input parameter) *
* Return : Returns Word of the given number as output */


Check it out if you required that.

How to USE?

Use the query to obtain the result:
select to_word(65475647) from dual;


The result will be:
Quote:
SIXTY-FIVE MILLION FOUR HUNDRED SEVENTY-FIVE THOUSAND SIX HUNDRED FORTY-SEVEN


Enjoy.
Cheers
Sanka
Re: Convertion from Number to Word [message #259201 is a reply to message #259180] Tue, 14 August 2007 11:31 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Rolling Eyes Question
Re: Convertion from Number to Word [message #259206 is a reply to message #259180] Tue, 14 August 2007 11:40 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Can you make that langauge specific please ? Laughing Laughing
Re: Convertion from Number to Word [message #259318 is a reply to message #259180] Wed, 15 August 2007 00:45 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Suppose we would scramble our answers to your questions here. What use would that have?
So why did you wrap the code? This implies you created this all by yourself, without using anybody elses help.
So, you did not use Tom Kytes examples? Not Barbaras script? Not any advise given here at this site?
Re: Convertion from Number to Word [message #259502 is a reply to message #259318] Wed, 15 August 2007 14:30 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
http://www.orafaq.com/forum/fa/449/0/ Good try, Sanka!

MHE
Re: Convertion from Number to Word [message #259704 is a reply to message #259502] Thu, 16 August 2007 05:13 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

Here is the original script.

CREATE OR REPLACE FUNCTION HR.TO_WORD(nNumber IN NUMBER) 
RETURN VARCHAR2 IS
	vToWord			VARCHAR2(4000);
	numToCharConv 	VARCHAR2(125);
	vPartText		VARCHAR2(1000);
	nCounter		NUMBER;
	bFlag			BOOLEAN;
CURSOR toWordCur(vText in VARCHAR2) IS
    SELECT LEVEL,
           SUBSTR(vText,
                  GREATEST(-LEVEL*6,-LENGTH(vText)),
                  LEAST(6,LENGTH(vText)-6*(LEVEL-1))) as strPart
    FROM DUAL
    CONNECT BY LEVEL <= CEIL(LENGTH(vText)/6)
    ORDER BY LEVEL;
BEGIN
		numToCharConv := nNumber;
		nCounter := 0;
		bFlag := FALSE;
	IF nNumber = 0 THEN
		RETURN('ZERO');
	ELSIF nNumber < 0 THEN
		numToCharConv := NULL;
	    numToCharConv := ABS(nNumber);
	    bFlag := TRUE;
	END IF;	
	FOR WordFetchCur IN toWordCur(numToCharConv)
		LOOP
			SELECT TO_CHAR(TO_DATE(WordFetchCur.strPart,'J'),'JSP')
			INTO vPartText  
			FROM DUAL;
			IF WordFetchCur.LEVEL = 2 AND LENGTH(numToCharConv) > 6 THEN
				vToWord := vPartText||' MILLION '||vToWord;
			ELSE
				vToWord := vPartText||' '||vToWord;
			END IF;		
		END LOOP;
	IF bFlag THEN
	   	RETURN('MINUS '||vToWord);
	ELSE
		RETURN(vToWord);
	END IF;	
EXCEPTION WHEN OTHERS THEN
	RETURN('NUMBER IS TOO LONG TO CONVERT.');	
END;

./fa/451/0/
Cheers
Sanka

[Updated on: Thu, 16 August 2007 05:14]

Report message to a moderator

Re: Convertion from Number to Word [message #259711 is a reply to message #259704] Thu, 16 August 2007 05:30 Go to previous messageGo to next message
gaurav.mcabanti
Messages: 5
Registered: May 2007
Junior Member
YOUR solution is jabardast........ dear sanka

[Mod-edit: Frank: Removed size-tags. Using colouring is irritating enough]

[Updated on: Thu, 16 August 2007 23:56] by Moderator

Report message to a moderator

Re: Convertion from Number to Word [message #259715 is a reply to message #259711] Thu, 16 August 2007 05:44 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I was right. It is a good try. Have a look at Tom Kytes variant (search for "spell the number" to get some ideas. It's one of the more popular threads over there.

MHE
icon14.gif  Re: Convertion from Number to Word [message #259725 is a reply to message #259715] Thu, 16 August 2007 06:03 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

Thanks Maheer. I'll do.

Cheers
Sanka
Re: Convertion from Number to Word [message #349458 is a reply to message #259180] Sun, 21 September 2008 23:26 Go to previous 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;
________________________________________________________________
Previous Topic: Executing dynamic SQL statements
Next Topic: Date list between two dates without base table
Goto Forum:
  


Current Time: Sun Dec 04 22:57:26 CST 2016

Total time taken to generate the page: 0.05721 seconds