1 person found this helpful
[oracle@localhost ~]$ sqlplus scott/tiger
SQL*Plus: Release 126.96.36.199.0 Production on Fri Dec 27 19:00:34 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Oracle Database 11g Enterprise Edition Release 188.8.131.52.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select 12345678901234567890 from dual;
SQL> select to_char(12345678901234567890,'9999999999999999999999') from dual;
You are Welcome.
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
Can someone please help me how to proceed with this.
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.
Thank You Ed, your explanation was very helpful.
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.