Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: February 09, 2002 - 10:38 pm UTC

Last updated: July 22, 2016 - 7:27 pm UTC

Version: 816

Viewed 100K+ times! This question is

You Asked

Hello Guru,
Q1) I need to display numbers from a database in a specific format.
Table Tn (n number(6,3));

The format is 999.999

SQL> insert into tn values( 123.123) ;
1 row created.
SQL> insert into tn values(0) ;
1 row created.
SQL>insert into tn values(0.123)
1 row created.
So I do
SQL> select to_char(n,999.999) from tn ;

TO_CHAR(
--------
123.123
.000
.123

I need the display output of value which is .000 to be 000.000 AND
.123 to be 000.123
as per the format mask.
No matter what mask i give zero is always missing from the left hand side of the decimal.
Or atleast I should have the display as 0.000 and 0.123.
How to do this.
2)Can u send me the link for available format masks for numbers and dates in the DOC Please.

Thanx



and Tom said...

ops$tkyte@ORA817DEV.US.ORACLE.COM> select to_char(n,'000.000') from t;

TO_CHAR(
--------
123.123
000.000
000.123


</code> http://docs.oracle.com/cd/A87860_01/doc/server.817/a85397/sql_elem.htm#34512 <code>

right there in the "SQL Reference Manual" along with all of the functions and such.

Rating

  (45 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

So simple Yet so difficult

A reader, February 10, 2002 - 4:19 pm UTC

Thanx for your soln and the link.Sometimes we get puzzled even for the simple problems and get overkilled trying to find complex answers to simple problems where the soln is almost there, the horse is there at the lake and TOM makes him drink the water.

Formating number in 8i

Sivababu, February 13, 2002 - 11:04 am UTC

Hello TOM,
I need to have 6 digits after decimal place. But it is for some calculations. I'm not able to get it using ROUND,TO_NUMBER and TO_CHAR.

For example
select TO_CHAR(9999999.6765722378,'999999999D9999999999') from dual;
O/P
9999999.6765722378
It is fine. When I will make it to number..
select TO_NUMBER( TO_CHAR(9999999.6765722378,'999999999D9999999999')) from dual;
O/P
9999999.676

Why it is comming only three decimal places. But i need 6 values after decimal. How can i achive this?.
I'm taking this value to insert into other database (Pervasive 2000).

Thanks and expecting ur reply.

with regards,
sivababu


Tom Kyte
February 22, 2002 - 8:56 am UTC

SQLPlus has a default number format is uses to display data on the screen "nicely".  You need to either 

o set the numformat to something you like
o use a TO_CHAR on the number with a format that applies in your case.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select   TO_NUMBER( TO_CHAR(9999999.6765722378,'999999999D9999999999'))   from 
  2  dual;

TO_NUMBER(TO_CHAR(9999999.6765722378,'999999999D9999999999'))
-------------------------------------------------------------
                                                   9999999.68

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> set numformat 9999999999999.9999999999999
ops$tkyte@ORA817DEV.US.ORACLE.COM> /

TO_NUMBER(TO_CHAR(9999999.6765722378,'999999999D9999999999'))
-------------------------------------------------------------
                                        9999999.6765722378000

ops$tkyte@ORA817DEV.US.ORACLE.COM> 


See the difference?  I would recommand using a TO_CHAR with an explicit format when selecting the data out or using numformat. 

to_char

mo, October 16, 2002 - 12:52 pm UTC

Tom:

Do you see anything wrong with this?

IF ( strfmt.strip_money(i_unit_price(i)) != to_char(i_old_unit_price(i),'999,999.99') ) THEN

PLS-00307 too many declarations of to_char match this call.

is it because of the array?

Thank you

Tom Kyte
October 16, 2002 - 1:13 pm UTC

what does i_old_unit_price's datatype.

dynamically defining scale via cast

Jonas Edgeworth, October 16, 2002 - 1:51 pm UTC

All the examples you've shown provide fixed scale conversions to char. Im curious how I can dynamically
specify the scale of a number (not converted to char)returned in a query. For example, Im trying to do something similar to the following:

create table t1 (
pk number primary key,
mycost number,
precision number(2)
);

insert into t1 (pk, mycost, precision) values (1, 102.1234, 2);
insert into t1 (pk, mycost, precision) values (2, 102.1235, 3);

select cast ( t.cost
AS NUMBER(30, precision))
from ( select t.cost, t.precision from t);

Which is throwing a ORA-01728 scale out of range exception.

Any thoughts?

Tom Kyte
October 16, 2002 - 1:58 pm UTC

ops$tkyte@ORA920.LOCALHOST> select t.mycost, round(t.mycost,precision)
  2    from t1 t;

    MYCOST ROUND(T.MYCOST,PRECISION)
---------- -------------------------
  102.1234                    102.12
  102.1235                   102.124


cast does not work like that, cannot look work like that.


the TYPE of a column must be the same for all rows -- you would be trying to change the datatype row by row.  that'll never fly. 

to_Char

mo, October 16, 2002 - 2:34 pm UTC

TOm:

i_old_unit_price is an IN parameter VARCHAR2.

What I am trying to do is compare a number entered via html form to another number retrieved from table, however because the database stores 99 without decimal, I am getting a difference if I compare
99 to 99.00 even though they should be the same.

so I want to format 99 to 99.00 and compare those.

Thank you,

Tom Kyte
October 16, 2002 - 4:03 pm UTC

no it is not, it is at LEAST and array:

to_char(i_old_unit_price(i),'999,999.99') ) THEN

but anyway, I'll assume it is an array of varchar2's then -- to_char( char_string ) isn't sensible. is that char_string a DATE or a NUMBER?

to_char( to_number( char_string ), '999,999' )


now, that might make sense

How about negative number in brackets

Charles Leung, May 30, 2003 - 2:25 am UTC

from SQL reference manual, I know that I can display the number in angluar bracket "<>" using "PR"

SQL> select to_char(-12434,'99,999PR') as neg_num from dual
SQL> /

NEG_NUM
--------
<12,434>

SQL> 

How can I display a number in "normal" bracket "(", i.e.

NEG_NUM
--------
(12,434) 

Tom Kyte
May 30, 2003 - 7:43 am UTC



translate( to_char(...), '<>','()' )

is one way.

How to display numbers in fixed significant numbers

A reader, February 13, 2004 - 10:55 am UTC

Tom,

Is there a way to display the numbers with only three significant numbers? Here is the situation:

In the table:
col1 Desired display
====== ==================
105125 ---> 105000
105687 ---> 106000
.011223 ---> .0112
.0012367 ---> .00124

Thanks,



Tom Kyte
February 13, 2004 - 11:15 am UTC

that is a really strange request. but, we can do:

ops$tkyte@ORA9IR2> select x, to_number( to_char( x, '9.99EEEE' ) ) from t
  2  /
 
         X TO_NUMBER(TO_CHAR(X,'9.99EEEE'))
---------- --------------------------------
    105125                           105000
    105687                           106000
   .011223                            .0112
  .0012367                           .00124
 
 

You are amazing, thank you so much!

A reader, February 13, 2004 - 11:47 am UTC


It is amazing

Shailandra, February 13, 2004 - 12:04 pm UTC

Tom,

This was amazing. Can you give the logic behind the above answer.

Tom Kyte
February 13, 2004 - 12:10 pm UTC

the to_char converted the number into a string, preserving only 3 digits using scientific notation (just run the to_char by itself to see what it does)

the to_number took this string and converted back into a number, there was only three digits left so thats all we get.




What about char columns?

Martin Guillen, October 11, 2004 - 3:31 pm UTC

Hi Tom: Is there a way to set sqlplus to show all character columns with a desired width or at least with a minimum but without setting each one with column command?
I mean:
SQL> create table narrow (notnarrowcolun char(1));

Table created.

SQL> insert into narrow values ('x');

1 row created.

SQL> select * from narrow;

N
-
x

I have a lot of tables with char(1) datatype and want to see the column name in the results.
I'm looking for something like "charformat" the numformat character type equivalent.

Thank you Tom,
Martin. 

Tom Kyte
October 11, 2004 - 4:58 pm UTC

there is not such a command, no. not in sqlplus

Change Number format

Mala, March 10, 2005 - 12:39 pm UTC

Hello Tom,

My requirement is the exact opposite of the follow-up dated Feb 13 2004. I have data as scientific format which I pull in as varchar2 into external file. It needs to be in complete number format for accurate calculations.


Measurement Needed Format
------------------------------
1.65E+04 16525
1.64E+04 16462
1.64E+04 16362
1.63E+04 16275
1.61E+04 16220
1.60E+04 16234

Thanks,


Tom Kyte
March 10, 2005 - 7:18 pm UTC

tell me -- how do you get from 1.65e+04 to 16525

maybe 16500

you don't have the digits there to get to what you want.

Mala, March 11, 2005 - 11:01 am UTC

Tom,
Thanks for the follow up..

The data comes from a measurement tool and is saved as a .dat or csv file which looks somewhat like this...

Measurements
----------------
S4010666_PRE_DIA
S4010666-2
24-Jan-04
13:59
24-Jan-04
13:59
1.60E+04
1.60E+04
1.61E+04
1.62E+04
1.63E+04
1.65E+04
1.66E+04
1.66E+04


When I open up this file in excel and change the format of the cells from scientific to number I get the complete reading like the column on the right in my original example.

However when I pull into an external table as is and convert to the data types I need, the last two digits for the scietific data rows become '00'.

FYI: I have set up the external table with just 1 varchar2(100) column, since I have mixed data types in the flat file.

Thanks again.

Tom Kyte
March 11, 2005 - 11:05 am UTC

sorry -- but if you have a text file with 1.66e+04 in it and excel shows you 16654 -- excel is making something "up" (where does the 54 come from? if what you say is true)

edit the file with notepad and see what you see

Mala, March 11, 2005 - 11:49 am UTC

In notepad I see all the digits !!!

954
5506
49
DIAMETER SCAN

--------------------
--------------------
21.430000
21.639999
* NONE
0.000000
23.000000
S4010666_PRE_DIA
S4010666-2
Jan 24, 2004
13:59
Jan 24, 2004
13:59
1.5960e+004
1.6701e+004
1.7015e+004
1.5061e+000
3.0000e+000
1.5987e+004
1.5960e+004
1.6093e+004

Wow.. I used to save the .dat file as .csv file and pull into external table. Instead I changed the external file to read the .dat file and it brings all the digits into oracle.. Some where during the conversion to csv file I am losing the rest of the number?? Can you explain it..

Just having a conversation with you solved my problem.. Thanks once again..

Mala

Tom Kyte
March 12, 2005 - 8:09 am UTC

AskExcel might be able to -- it is happening via your conversion in excel...

Space in the output of formatted result

Sunil Reddy, March 11, 2005 - 12:09 pm UTC

Hi Tom,

Thank you very much for your help to Oracle world.

I have a problem with the output of to_char(n, '000.000'). I am getting a leading space in the output. I have pasted the results of the queries for your reference.

select to_char(n, '000.000') result from tn;
RESULT
---------
123.123
000.000
000.123

select trim(to_char(n, '000.000')) result from tn;
RESULT
---------
123.123
000.000
000.123

I am using Oracle version 9.2.0.2.0.

Thanks for the help.

Regards,
Sunil.

Tom Kyte
March 12, 2005 - 8:13 am UTC

to_char(n, 'fm000.000')

What about this format?

A reader, March 28, 2005 - 6:19 am UTC

Hi Tom I need a format like this:

123456
123.45
1.235
0.55
1254.423

Here you can see that decimal point is somewhat aligned & there are no trailing zeros. So what I need is to avoid trailing zeros & still align the decimal position
Thanks

Tom Kyte
March 28, 2005 - 7:59 am UTC

ops$tkyte@ORA9IR2> select x, lpad(trunc(x),6) || rpad(decode(x-trunc(x),0,' ',x-trunc(x)),4) b
  2    from t;
 
         X B
---------- ----------
    123456 123456
    123.45    123.45
     1.235      1.235
       .55      0.55
  1254.423   1254.423
 


is one approach. 

What about Oracle 8.0.5

A reader, March 28, 2005 - 9:08 am UTC

Thanks for the quick response. right now I don't have oracle but could you please tell me whether it will work on oracle 8.0.5 becasue I dont think there any function as x-trunc in 8.0.5
Thanks alot

Tom Kyte
March 28, 2005 - 9:14 am UTC

x was a column in a table T

x-trunc(x)

is

"column x" minus the trunc( "column x" )

and exists just fine in 805

Sorry

A reader, March 28, 2005 - 9:11 am UTC

Sorry for the inconvenience I created from previous followup. It was just a sight illusion ;)

thanks it is gonna work

Thanks & sorry for annoyance

A reader, May 10, 2005 - 3:15 pm UTC

Hi Tom,

I have a table with a number column

create table t1
(
id number
);

When I try to display the id, I am getting the output as follows:

> select max(id) from t1;

MAX(ID)
-------
8.7183E+10

How do I display this in the numeric format. Please help.

Thanks.

Tom Kyte
May 10, 2005 - 11:16 pm UTC

select to_char( max(id), '99999999999999999999' ) from t1;

or

set numformat 99999999999999999999999.99999999
select max(id) from t1;



Formatting percentages

VA, June 16, 2005 - 8:51 am UTC

Maybe I am missing something but I couldnt find a way to format a number as a percentage between 0.00% and 100.00%

I tried

select to_char(col,'990.90%') from dual

but it errors out with 'invalid number format model'

Thanks

Tom Kyte
June 16, 2005 - 9:59 am UTC

select to_char(col,'990.00')||'%' from

Formatting percentages

A reader, June 16, 2005 - 10:38 am UTC

Bummer, I was hoping I could do that using a format mask because I am trying to use it in a HTML DB query region.

Even '990.90"%"' doesnt seem to work.

Oh well, thanks for your help

how to format these numbers ?

parag j patankar, June 05, 2006 - 6:50 am UTC

Hi Tom,

I am having amounts in currency. Some of amounts are quite large. I want output of these records as it is stored in table like below

-113804083029
100.20
30000.25
3233

I just want to display amounts as it has been saved. But due to large amounts it get converted to Scientific figures. for e.g figure -113804083029 get converted into -1.138E+09. How to avoid this using format clause ?

thanks & regards
pjp


Tom Kyte
June 05, 2006 - 7:51 am UTC

sqlplus has a default number format for display purposes.

I assume you are using sqlplus.


SQL> set numformat 99999999999999.99

or whatever you like (heck, use to_char to be "sure" it works in everyones sqlplus if you are createing reports/scripts) 

do not want to add additional decimal points when not there

Parag Jayant Patankar, June 05, 2006 - 8:07 am UTC

Hi Tom,

Sorry, my information was not complete. I am using SQLPLUS for displaying values. As I have mentioned these amounts in currencies, so it does not make any sense to add decmial places for e.g JPY

-113804083029
100.20
30000.25
3233

So is there any way to do this. because if I do format then it will addional decmial points !!! Can any other way I can avoid this ?

thanks & regards
pjp

Tom Kyte
June 05, 2006 - 9:39 am UTC

what do you mean "additional" decimal points.

You would be showing a CONSISTENT set of decimal points (eg: always two of them"


In sqlplus, you  use 

SQL> set numformat <whatever)

or you use to_char.


ops$tkyte@ORA10GR2> select * from t;

         X
----------
-1.138E+11
     100.2
  30000.25
      3233

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select to_char( x, '999999999999999.99' ) from t;

TO_CHAR(X,'99999999
-------------------
   -113804083029.00
             100.20
           30000.25
            3233.00


or if you want

ops$tkyte@ORA10GR2> select case when trunc(x)=x
  2              then to_char( x, '999999999999999999' )
  3              else to_char( x, '999999999999999.99' )
  4              end x
  5     from t;

X
-------------------
      -113804083029
             100.20
           30000.25
               3233

 

Is it Possible?

A reader, June 12, 2006 - 10:03 am UTC

Okey Tom i have a Doubt

Is it possible to store 0.00 in field NUMBER(5,2)?

If no why, if yes how?

Thanks,

Suren

Tom Kyte
June 13, 2006 - 9:58 am UTC

0.00 is a string

0 is a number


if you insert the number zero, it is quite trivial to get the string 0.00


ops$tkyte@ORA10GR2> select to_char(x,'990.00') from t;

TO_CHAR
-------
   0.00
 123.45
 

Display Numbers as a range

Seema, July 14, 2006 - 11:57 am UTC

Hi Tom,


I have a the following numbers stored in a table. Example:
1
2
3
6
9
12
13
15
16
17
18
19
30
32

I need to diaplay these as a range. If there are more than 3 line items in a sequence, then itÂ’ll be shown as a range - otherwise an enlisting.

i.e. the result in this example should be:
1-3,6,9,12,13,15-19,30,32
Please advise how I can achieve this. I have to do this inside a stored procedure. Thanks a lot.
____________________________
create table t (n number);
insert into t values(1);
insert into t values(2);
insert into t values(3);
insert into t values(6);
insert into t values(9);
insert into t values(12);
insert into t values(13);
insert into t values(15);
insert into t values(16);
insert into t values(17);
insert into t values(18);
insert into t values(19);
insert into t values(30);
insert into t values(32);
commit;


format the numbers for diaplay

Seema, July 20, 2006 - 12:01 pm UTC

I cracked the above problem. So thank you.



Positive.Negative Number

Mita, August 10, 2006 - 12:28 pm UTC

how do I get the +/- sign using to_char(number) ??

data
123.123
-234.234

output
+123.123
-234.234




Tom Kyte
August 10, 2006 - 12:58 pm UTC

ops$tkyte%ORA10GR2> select to_char( 1, 's999') from dual;

TO_C
----
  +1

ops$tkyte%ORA10GR2> select to_char( -1, 's999') from dual;

TO_C
----
  -1



you might want to check out the sql reference guide - lots of formats for you! 

Good Inputs

atul, April 10, 2007 - 6:40 am UTC

Is there any way to show results in a Horizontal format?


Normal Result is as follows:

select name||id from emp;

NAME||ID
-----------------------------------------------------------
test,
test,
test,

I want result to be displayed horizontal like

test,test,test

Thanks in advance

Tom Kyte
April 10, 2007 - 10:51 am UTC

search site for

stragg

for more than one way to do that - from a user defined aggregate to a connect by solution.

Decimals to display, unique to a given row

Steve, April 25, 2007 - 5:13 pm UTC

Tom,

I've got numeric data that is used in mathematical calculations. So I'm dead set on storing it as a number. However, the customer wants to preserve the number of decimals input in the application as this denotes significant precision to the end user, so if they enter 9.0000, they want it to display 9.0000 not 9 or even 9.0, or 9.1200 as 9.1200 not 9.12. The input could vary for any given row. The dominant proposed solution is to store the data in a varchar2 format and let the application ensure data integrity. I'm strongly opposed to this. I'm wondering if there's a way to do this with a user defined type where the application passes a string to Oracle which gets handled by the user defined object to store the number and the format by parsing the string?
Tom Kyte
April 25, 2007 - 5:35 pm UTC

sounds like you want to store as a string, but verify it is in fact a number


ops$tkyte%ORA10GR2> create table t ( x varchar2(20) check (to_number(x) <> null));

Table created.

ops$tkyte%ORA10GR2> insert into t values ( '9.00000' );

1 row created.

ops$tkyte%ORA10GR2> insert into t values ( null );

1 row created.

ops$tkyte%ORA10GR2> insert into t values ( 'a' );
insert into t values ( 'a' )
*
ERROR at line 1:
ORA-01722: invalid number


ops$tkyte%ORA10GR2> select rownum, x from t;

    ROWNUM X
---------- --------------------
         1 9.00000
         2

ops$tkyte%ORA10GR2>

Re: Parag Jayant Patankar question

Nopparat V., April 26, 2007 - 1:03 am UTC

from Parag Jayant Patankar question, this may answer him.

SQL> select * from t ;

          N1
------------
-1.13804E+11
       100.2
    30000.25
        3233

SQL> set numwidth 15
SQL> select * from t ;

             N1
---------------
  -113804083029
          100.2
       30000.25
           3233

SQL>

me too - use a numeric check constraint, Thanks

Steve, April 26, 2007 - 10:30 am UTC

Thanks Tom!
After posting my earlier question, I asked myself, if I have to store it in varchar2 what's the best way to insure numeric data integrity? I came up with the same answer a Check Constraint. Your suggestion adds much validity to the solution! I played around with scientific notation just for fun.

SQL> create table t1(a1 varchar2(50));

Table created.

SQL> ALTER TABLE t1 ADD (
2 CONSTRAINT CK_a1_numeric
3 CHECK (a1*0=0));

Table altered.

SQL> insert into t1 values (null);
SQL> insert into t1 values ('1');
SQL> insert into t1 values ('1.0000');
SQL> insert into t1 values ('00001.0012322133154400');
SQL> insert into t1 values ('0.992938912838700000000000010012322133154400');
SQL> insert into t1 values ('1.2E+02');
SQL> insert into t1 values ('1.2E+23');
SQL> insert into t1 values ('100000');
SQL> insert into t1 values (' 1000007');
SQL> insert into t1 values ('x');
insert into t1 values ('x')
*
ERROR at line 1:
ORA-01722: invalid number
SQL> select * from t1;

A1
--------------------------------------------------

1
1.0000
00001.0012322133154400
0.992938912838700000000000010012322133154400
1.2E+02
1.2E+23
100000
1000007
-6523.65200000

SQL> select a1*1 from t1;

A1*1
----------

1
1
1.00123221
.992938913
120
1.2000E+23
100000
1000007
-6523.652

SQL> select a1/100000000000000000000 from t1;

A1/100000000000000000000
------------------------

1.0000E-20
1.0000E-20
1.0012E-20
9.9294E-21
1.2000E-18
1200
1.0000E-15
1.0000E-14
-6.524E-17

adding problem

Fantasy, July 09, 2007 - 2:51 am UTC

the problem is like this...

as i add the total up the amount doesn't give the correct amount for example...

2.25 + 2.25 + 2.25 + 2.25 + 0.50 = 9.50

but it gives me the result 9

the coding are:

"sum(to_number(DEC_NOHOURS)) dec_hours, sum(OT_AMOUNT) amt" 


thanks...sorry if there is inconvenient... :)


Tom Kyte
July 09, 2007 - 7:01 am UTC

show us how to reproduce this.

re: adding problem

Fantasy, July 09, 2007 - 11:31 pm UTC

dec_nohours ot_amount
----------- ---------
1 2
1 2
1.50 3.50

add it up

output:

dec_nohours = 3
amt = 7

expected output is:

dec_nohours = 3.50
amt = 7.50

the coding used here is:

"sum(to_number(DEC_NOHOURS)) dec_hours, sum(OT_AMOUNT) amt" 


thanks...
Tom Kyte
July 10, 2007 - 11:49 am UTC

no create table
no insert intos

NO LOOK

but, it would not work as you describe - so, you'll have to show us exactly how to reproduce.

period.

re: adding problem

fantasy, July 10, 2007 - 4:04 am UTC

sorry tom...

i forget to mention that the DEC_NOHOURS and OT_AMOUNT are data from Microsoft Access...in Microsoft Access DEC_NOHOURS is written as DEC HOURS with no underscore as well as OT_AMOUNT is written as OT AMOUNT

thanks...
Tom Kyte
July 10, 2007 - 11:53 am UTC

so, show us

a) the select * that displays this data
b) the query that fails to output the correct amount

cut and pasted right from sqlplus.

re: adding problem

fantasy, July 10, 2007 - 8:34 pm UTC

"select PERSON, replace(namex_1,"","_') name, " _
"decode(HOURCAT , '9100', ", '9210', ", '9310', ", '9410', "," _
"'9510', ", '9610', ", HOURCAT)/1000 hour, woty_desc allw_desc," _
"sum(to_number(DEC_NOHOURS)) dec_hours, sum(OT_AMOUNT) amt " _
"from eng_overallw where woty_desc is not null group by person, namex_1, hourcat, woty_desc"



re:adding problem

fantasy, July 10, 2007 - 8:52 pm UTC

the query in the access works fine...it gives the correct amount

as we compare it to the report between access and oracle.it don't reconcile in the oracle...

in the access's report it written as 22.50 but in the oracle's report it written as 22.00


Tom Kyte
July 10, 2007 - 10:50 pm UTC

you have entirely missed the point.

please, give us something we can cut and paste and reproduce.

access - little reporting front end.

oracle - a database that processes sql

access - a little reporting front end that can submit sql to oracle.
oracle - something a tool like access can say "please run some sql for me"


you'll really have to either

a) show us how to reproduce this
b) see a)

re: adding problem

fantasy, July 11, 2007 - 3:25 am UTC

thanks tom...

i appreciate your help...

for the moment i have a little problem here...

i will write a review soon about this...

thanks again...

what is PR?

A reader, July 11, 2007 - 4:27 pm UTC


Tom,

I know what PR format does...it gives the angle brackets <> to negative numbers...out of curiosity...what does abbreviation PR stands for? I do not seem to find it anywhere.

Format in pure scientific notation

Claude, January 21, 2008 - 2:36 pm UTC

Hi Tom,
None of the examples above showed "pure" scientific notation; that is placing an "E" after the last significant digit (without knowing in advance where that would be) and making the exponent the number of places to shift over the decimal.

For example:
1234.567 => 1234567E-3
12000 => 12E3
8765 => 8765E0

Does SQL support this number format and what would it be?
Thnx

WH, July 09, 2008 - 11:52 pm UTC

Hi ,

I need to display sql spool output in following format.

start|187027.7607131123288810600408685954775613|end
start|0.5|end

I'm currently using following sql and I want to display 0.xxxx for .xxxx data.

create table test1(
c1 varchar2(100),
c2 NUMBER,
c3 varchar2(100));


insert into test1 values('start',187027.7607131123288810600408685954775613,'end');
insert into test1 values('start',0.5,'end');
commit;

select c1||'|'|| cast(c2 as varchar2(4000)) ||'|'||c3 from test1;
start|187027.7607131123288810600408685954775613|end
start|.5|end

--the reason using CAST is not to loose any decimal places.
--without CAST will spool out up to 40 characters max.


thanks,




Tom Kyte
July 10, 2008 - 7:24 am UTC

to_char would have been my first approach

ops$tkyte%ORA11GR1> select c1||'|'||
  2         case when c2 < 1 then 0 end || to_char(c2) || '|' ||
  3         c3
  4    from test1
  5  /

C1||'|'||CASEWHENC2<1THEN0END||TO_CHAR(C2)||'|'||C3
-------------------------------------------------------------------------------
start|187027.760713112328881060040868595477561|end
start|0.5|end


going further

A reader, July 17, 2008 - 8:59 pm UTC

in the example above, if i do a substr after casting it or changing it to_char why does it lose the decimal points, dont wanna use trunc as its still rounding off

Tom Kyte
July 18, 2008 - 4:14 pm UTC

if you do a substr, umm, you have a string, there is no such thing as a decimal point in a string.

I did not use 'trunc', so I really don't know what you mean.

I am looking for the way, when number field may be NULL

Manu Batham, April 04, 2014 - 2:59 pm UTC

Tom,

As you said in one of the previous comments

select to_char(col,'990.00')||'%' from dual

The solution you provided will always concatenate '%', but what if my number col is NULL, I don't want to show % in that case. I am asking right now number and % sign are independent, no matter what the number is percent sign will be there. I don't want to use null functions, as it will be extra step of calculation, any other suggestion you can recommend.

Thanks,
Manu
Tom Kyte
April 16, 2014 - 6:52 pm UTC

ops$tkyte%ORA11GR2> select rownum, nvl2( x, to_char(x,'990.00')||'%', null )
  2    from (select '134.32' x from dual union all select null x from dual)
  3  /

    ROWNUM NVL2(X,T
---------- --------
         1  134.32%
         2

ops$tkyte%ORA11GR2> 

A reader, November 03, 2014 - 2:15 pm UTC

you can set numformat 999.999
or select to_char(n,999.999) from table

Column with value zero!!

A reader, June 16, 2015 - 8:20 pm UTC

I am using to_char function to display column values as required. Everything worked fine except the fact when a column has zero value. Suppose a column with datatype as (13,2) exists and has values 123.45,123 and 0. Now I want to display the result as below:

123.45 should display as 123.45
123 should display as 123.00
0 should display as 0

To summarise I want column values to be displayed on column datatype style( Decimal is the concern) but when column has zero value then value displayed should be zero (No decimal should be displayed). Please advise.

Mask

Denilton, July 04, 2016 - 3:33 pm UTC

The comando select
trim(to_char(HEADER.INVOICE_AMOUNT,'999G999G999G990D99999999999999','nls_numeric_characters = '',.''')) VL_LIQ,
HEADER.* from apps.CLL_F189_INVOICES_INTERFACE HEADER
where HEADER.INTERFACE_INVOICE_ID = 1108087
AND HEADER.source = 'PSFRETE'
return 720,95000000000005 versus 720,95. 720.95 is correct value. Why?
Chris Saxon
July 04, 2016 - 3:46 pm UTC

When you say 720.95 is the correct value do you mean:

- That's the output you want or
- You know that's the value stored in header.invoice_amount?

If 720.95000000000005 is the actual value, then just round to remove the trailing 5. If not, give us a test case showing this giving the wrong value:

create table t (
  x number
);

insert into t values (720.95000000000005);
insert into t values (720.95);

select trim(
  to_char(x,'999G999G999G990D99999999999999','nls_numeric_characters = '',.''')
), trim(
  to_char(round(x, 2), '999G999G999G990D99999999999999','nls_numeric_characters = '',.''')
)
from t;

TRIM(TO_CHAR(X,'999G999G999G990 TRIM(TO_CHAR(ROUND(X,2),'999G99
------------------------------- -------------------------------
720,95000000000005              720,95000000000000             
720,95000000000000              720,95000000000000 


Decimal without decimal point

Srinivasan, July 22, 2016 - 6:02 pm UTC

Hi Tom,
I want to insert a decimal number which has no decimal point into a table with a decimal point. Is there a predefined function or a way to do this?

Ex: (46926831654) should be inserted as (469268.31654)

I dont want to use the string concatenation with decimal since I suspect it will reduce my code efficiency.

Thanks
Connor McDonald
July 22, 2016 - 7:27 pm UTC

No there is no predefined function.

So I assume it is *always* 5 decimal points ? In that case, you could insert :value/100000