4 Replies Latest reply: Dec 31, 2013 8:09 AM by 968751 RSS

number format to string format

968751 Explorer
Currently Being Moderated

Hello guys, in a sql I am trying to convert a number format to string format.

 

create table test_cpy (name varchar2(20), age number(2), salary number(35));

 

insert into test_cpy values ('a',20,3746583);

 

select name||','||age||','||'="'+salary+'"' from test_cpy;

 

The reason for the number to text conversion is because the data in the table will be exported to another file in csv format and will be viewed in excel sheet and the numbers with large value will have an exponential value to it and I want to display the entire number as it is. The only to do that is to convert the number to text format.

 

To convert the number format to string format I used the following.

 

select name|| ',' || age || ',' || '="'+salary+'"' from test_cpy;

(the above sql is giving me an error ora-01722:invalid number)

 

I tried select name|| ',' || age || ',' || to_char(salary) from test_cpy;

but when I finally view the data in the excel, I am still getting the exponential values instead of the actual numbers

5.03479E+15

 

Can someone please help me how to proceed with this.

 

Thank You

  • 1. Re: number format to string format
    sb92075 Guru
    Currently Being Moderated

    [oracle@localhost ~]$ sqlplus scott/tiger

     

     

    SQL*Plus: Release 11.2.0.2.0 Production on Fri Dec 27 19:00:34 2013

     

     

    Copyright (c) 1982, 2010, Oracle.  All rights reserved.

     

     

     

     

    Connected to:

    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

    With the Partitioning, OLAP, Data Mining and Real Application Testing options

     

     

    SQL> select 12345678901234567890 from dual;

     

     

    12345678901234567890

    --------------------

              1.2346E+19

     

     

    SQL> select to_char(12345678901234567890,'9999999999999999999999') from dual;

     

     

    TO_CHAR(123456789012345

    -----------------------

       12345678901234567890

     

    You are Welcome.

  • 2. Re: number format to string format
    EdStevens Guru
    Currently Being Moderated

    968751 wrote:

     

    Hello guys, in a sql I am trying to convert a number format to string format.

     

    create table test_cpy (name varchar2(20), age number(2), salary number(35));

     

    insert into test_cpy values ('a',20,3746583);

     

    select name||','||age||','||'="'+salary+'"' from test_cpy;

     

    The reason for the number to text conversion is because the data in the table will be exported to another file in csv format and will be viewed in excel sheet and the numbers with large value will have an exponential value to it and I want to display the entire number as it is. The only to do that is to convert the number to text format.

     

    To convert the number format to string format I used the following.

     

    select name|| ',' || age || ',' || '="'+salary+'"' from test_cpy;

    (the above sql is giving me an error ora-01722:invalid number)

     

    I tried select name|| ',' || age || ',' || to_char(salary) from test_cpy;

    but when I finally view the data in the excel, I am still getting the exponential values instead of the actual numbers

    5.03479E+15

     

    Can someone please help me how to proceed with this.

     

    Thank You

    You are wasting your time trying to solve an Excel presentation issue with Oracle.  The fact that you are putting the data into a .csv file means that -- in that file -- everything is a text string.  Either you use to_char for an explicit conversion, or oracle will call it internally for an implicit conversion using default values.  Result is the same.  A .csv file is nothing but a text file.  And Excel will always try to analyze and interpret the data by its own rules. By those rules, a text string that looks like a number (nothing but numeric characters) will get converted to a number.  The scientific notation is purely a function of the column width that Excel uses by default.  Make the column wider and voila, the scientific notation goes away and the full number is visible.    Sorry, but there's nothing Oracle can do about how Excel works.

  • 3. Re: number format to string format
    968751 Explorer
    Currently Being Moderated

    Thank You Ed, your explanation was very helpful.

  • 4. Re: number format to string format
    968751 Explorer
    Currently Being Moderated

    actually if you do the following

     

    select name||','||age||','|| '="'||salary||'"'  from test_cpy;

    the csv file when viewd in excel would display the numbers in text format.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points