Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2007
    Posts
    4

    Unanswered: numbet to string

    can anyone plz tell me that which function to use for converting number value to string in oracle9i

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Although it is a BAD habbit, sometimes you can rely on implicit datatype conversion. See this example: I'll create a table and insert a NUMBER into it (although a column is of a CHARACTER datatype).
    Code:
    SQL> create table test (col varchar2(20));
    
    Table created.
    
    SQL> insert into test (col) values (12345);    --> inserting a number
    
    1 row created.
    
    SQL> select * from test where col = 12345;
    
    COL
    --------------------
    12345
    Now I'll insert another record, but this time alphanumeric value. It will be successful (of course):
    Code:
    SQL> insert into test (col) values ('abc12345');
    
    1 row created.
    However, further relying on implicit conversion will no longer work - you'll have to deal with characters:
    Code:
    SQL> select * from test where col = 12345;
    ERROR:
    ORA-01722: invalid number
    
    
    
    no rows selected
    
    SQL> select * from test where col = '12345';     --> enclose it into single quotes
    
    COL
    --------------------
    12345
    
    SQL> select * from test where col = to_char(12345);   --> use function Shammat suggested
    
    COL
    --------------------
    12345
    
    SQL>
    Conclusion? Always take care about datatype conversion. Don't trust Oracle as it will fool you sooner or later.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •