1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

query to convert number to alphabetical format reg.

Discussion in 'SQL PL/SQL' started by laxman, Apr 19, 2010.

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Hi all,
    i have very very small doubts to get clarify ,let say i am having data 1,2,3 up to 1000 , then i want to write one query to convert this data into alphabetical format i.e in output all data should display like one,two,................,thousand.
    can anybody help me to write this query.

    Thanks n regards
    Laxman
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Understanding the to_char() function will help you tremendously in this task:

    Code (SQL):
     
    SQL>
    SQL> DECLARE
      2        numtext varchar2(4000);
      3  BEGIN
      4        FOR i IN 1..10000 loop
      5  
      6         SELECT LOWER(to_char(to_date(i,'J'), 'JSP'))
      7         INTO numtext FROM dual;
      8  
      9         dbms_output.put_line(i||' is written as '||numtext);
     10  
     11        END loop;
     12  END;
     13  /
    1 IS written AS one                                                            
    2 IS written AS two                                                            
    3 IS written AS three                                                          
    4 IS written AS four                                                            
    5 IS written AS five                                                            
    6 IS written AS six                                                            
    7 IS written AS seven                                                          
    8 IS written AS eight                                                          
    9 IS written AS nine                                                            
    10 IS written AS ten                                                            
    11 IS written AS eleven                                                        
    12 IS written AS twelve                                                        
    13 IS written AS thirteen  
    ...
     
     
  3. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Thanks a lot sir for your valuable information. also i am interested to know that why we need 'JULIAN' format to convert number to word but let me try to google this point and get more information on this.

    Regards
    Laxman
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Another Interesting link is here Asktom
     
  5. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    thanks a lot sir,now i clear with the concept.:)

    regards
    Laxman