Solved

How to convert Nubmer into words?

Posted on 2001-08-12
7
2,642 Views
Last Modified: 2012-06-21
I need to convert number into words. e.g 325 into Three Hundred and Twenty Five.
The width is number(15,2). Can some body do this? As I am new to ORACLE
it is quite difficult for me.

Wainting for your reply.
Thanks
Abdul Qadir Khan
0
Comment
Question by:aqk139
7 Comments
 
LVL 2

Accepted Solution

by:
AllaI earned 200 total points
ID: 6377564
Select to_char(to_date(325,'J'),'JSP') from dual;
0
 
LVL 3

Expert Comment

by:Wadhwa
ID: 6378048

Converting numbers to words


to_char(to_date(<n>,'J'),'JSP')
will give amount <n>in words.
You can convert amounts less than one million using the above
function.
If you want to convert larger amounts,use the following function

You can create this function in the database and use it in the select
statement to convert the amount into words.(You can use this function
in Oracle client tools,for example Developer/2000 or SQL*Plus.)
The following are two examples:
select name,salary,to_words(salary)from emp_master;
select name,salary,initcap(to_words(salary))from emp_master;
FUNCTION
------PL/SQL
------function to_words(amount)-----
------This function is tested with Oracle 7.3.2 database.
create or replace function to_words(n in number)return varchar2
as
a varchar2(1??):=to_char(n);
tmp_word varchar2(5??);
out_word varchar2(1???):=null;
type varchar2_tab is table of varchar2(5?)index by binary_integer;
magtab varchar2_tab;
function to_word3(a3 in varchar2,magindex in number :=?)return
varchar2 as
tmp_word3 varchar2(1??);
begin
if length(a3)>3 then
if to_number(substr(a3,length(a3)-2))=? then
tmp_word3 :=null;
else
tmp_word3 :=to_char(to_date(substr(a3,
length(a3)-2),
'J'),'JSP');
end if;
else
tmp_word3 :=to_char(to_date(a3,'J'),'JSP');
end if;
if tmp_word3 is null then
return(null);
else
return(tmp_word3||''||magtab(magindex));
end if;
end;
begin
if a is null then
return(null);
end if;
magtab(?):='';
magtab(1):='THOUSAND';
magtab(2):='MILLION';
magtab(3):='BILLION';
magtab(4):='TRILLION';
magtab(5):='QUADRILLION';
magtab(6):='QUINTILLION';
for i in ?..6 loop
out_word :=to_word3(a,i)||''||out_word;
if length(a)<=3 then
return(out_word);
end if;
a :=substr(a,1,length(a)-3);
end loop;
return('***Amount >=1??? quintillions***');
end;
/

The above code is from 'Oracle Update'

Hope this helps
Sameer
0
 
LVL 2

Expert Comment

by:RMZ
ID: 6379506
SQL> Select to_char(to_date(325,'J'),'JSP') from dual;

TO_CHAR(TO_DATE(325,'J'),
-------------------------
THREE HUNDRED TWENTY-FIVE
---rmz---
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

Expert Comment

by:schwertner
ID: 6379739
1* select to_char( to_date(5373484,'J'),'Jsp') from dual
SQL> /

TO_CHAR(TO_DATE(5373484,'J'),'JSP')
--------------------------------------------------------------------------
Five Million Three Hundred Seventy-Three Thousand Four Hundred Eighty-Four

This will work for numbers between 1 and 5,373,484...
You can take it a step further to support numbers -5,373,484 .. 5,373,484 by
select decode( sign( :N ), -1, 'Negative ', 0, 'Zero', NULL ) | |
decode( sign( abs(:N) ), +1, to_char( to_date( abs(:N),'J'),'Jsp') )
from dual
/
That covers the integer numbers.


SELECT TO_CHAR(TO_DATE(1234567,'J'),'JSP')
FROM dual
That's the maximum.

Try using the spelcheck.sql file found in chapter 5 of the book 'Oracle8 PL/SQL programming' by Scott Urman. The maximum no you can spell out is 10**10.
0
 

Expert Comment

by:raghav3
ID: 6456673
Hi this will work


1. run this statement in sql prompt.
  SQL> Select to_char(to_date(&Value,'J'),'JSP') from dual;

2. It asks:-

  SQL>
  Enter value for value: 255

3. Answer is
  SQL>
      VALUE
      ----------------------
      TWO HUNDRED FIFTY-FIVE


Thanks
Raghav
0
 
LVL 5

Expert Comment

by:Netminder
ID: 6767202
I have rejected the proposed answer.

raghav3,

Welcome to Experts Exchange!

It is common practice for Experts to post their responses to questions in the form of a Comment, rather than an Answer. The reasoning is straightforward; EE is a collaborative site. By proposing an Answer, you essentially lock others out of the collaborative process by making it more difficult for other Experts to see the question.

You should also only propose an Answer when you are absolutely certain that yours is the one-and-only, single solution to the problem, AND that your response does not paraphrase or restate someone else's suggestion, AND that your response is a complete and total solution.

There has been no activity in this question in quite some time, and it looks like it has been abandoned. As part of our ongoing mission to clean up the topic areas, a Moderator will finalize this question within the next seven (7) days. At that time, either I or one of the other Moderators will force/accept the comment of schwertner.

DO NOT ACCEPT THIS COMMENT AS AN ANSWER. If you have further comments on this question or the recommendation, please leave them here.

aqk139,

Some of these questions have been open for some time, and records indicate you logged in as recently as last week. Please resolve them appropriately as soon as possible. Continued disregard of your open questions will result in the force/acceptance of a comment as an answer; other actions affecting your account may also be taken. I will revisit these questions in approximately seven (7) days.

http://www.experts-exchange.com/jsp/qShow.jsp?ta=msaccess&qid=20202508
http://www.experts-exchange.com/jsp/qShow.jsp?ta=crystal&qid=20255526
http://www.experts-exchange.com/jsp/qShow.jsp?ta=oracle&qid=20167212
http://www.experts-exchange.com/jsp/qShow.jsp?ta=oracle&qid=20165928
http://www.experts-exchange.com/jsp/qShow.jsp?ta=oracle&qid=20141926
http://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20243322
http://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20207867
http://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20207526
http://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20191215
http://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20183720
http://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20180355
http://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20175537
http://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20175003

Thanks,

Netminder
Community Support Moderator
Experts Exchange
0
 
LVL 5

Expert Comment

by:Netminder
ID: 6821359
Admin notified of user neglect. Force/accepted by

Netminder
Community Support Moderator
Experts Exchange
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now