conversion

[saiprasadr]

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]

[sambavan]

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