conversion
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: conversion

  1. #1
    Join Date
    Mar 2001
    Posts
    3

    Exclamation

    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]

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
  •  


Click Here to Expand Forum to Full Width