-
hi
i want to spell a number
ex 1345
one thousand three hundred fourty five
only with sql statements
(using to_char,to_date & some date format)
how can i achive this
help me
sai prasad.
[Edited by saiprasadr on 03-26-2001 at 04:32 PM]
-
Here are some examples:
SQL Code to Print Numbers as Words:
===================================
USABLE SELECT
~~~~~~~~~~~~~
select decode(floor(amount),0,'ZERO',to_char(to_date(floor(amount),'J'),
'JSP'))||' POUNDS AND '||
decode(mod(amount*100,100),0,'ZERO',to_char(to_date(mod(amount*100,100),'J'),
'JSP'))||
' PENCE'
from cheques
where amount is not null;
The same restrictions apply to the maximum number this can handle.
3,442,447 is the highest Julian date figure Oracle can convert.
DATABASE CURRENCY SYMBOL
~~~~~~~~~~~~~~~~~~~~~~~~
The following code is a starting point to use the currency symbol set for
each session. The current currency setting is provided by the database view
NLS_SESSION_PARAMETERS.
1. Use this select statement within a PL/SQL block:
select NLS_CURRENCY
into currency
from NLS_SESSION_PARAMETERS;
2. The following decode statement converts currency symbols into words.
Add your symbol and wording as required and replace the word POUNDS in
the first select with:
decode(currency,'$','DOLLARS','£','POUNDS','¥','YEN','DM','MARK',currency)
3. Do the same for the PENCE:
decode(currency,'$','CENTS','£','PENCE','¥',' ','DM','Pfennige',currency)
4. Wrap the above in a PL/SQL block.
This was an example from metalink
Good luck
Sam
Thanx
Sam
Life is a journey, not a destination!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|