René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

set numformat [SQL*Plus]

set numformat 999
set numformat 009

set numformat 999.99
set numformat 009.99

set numformat ""
Sets the number format for selected numbers (unless overridden by column format). The format is reset with "". 
set feedback off
set pagesize 0

create table numformat_ex (
  id    number primary key,
  num_1 number,
  num_2 number(5),
  num_3 number(7,2)
);

insert into numformat_ex values (    0,     1    ,     1    ,     1    );
insert into numformat_ex values (    1,    11    ,    11    ,    11    );
insert into numformat_ex values (    2,   111    ,   111    ,   111    );
insert into numformat_ex values (    3,  1111    ,  1111    ,  1111    );
insert into numformat_ex values (    4, 11111    , 11111    , 11111    );
insert into numformat_ex values (    5,     1.1  ,     1.1  ,     1.1  );
insert into numformat_ex values (    6,     1.11 ,     1.11 ,     1.11 );
insert into numformat_ex values (    7,     1.111,     1.111,     1.111);

insert into numformat_ex select   8+id, - num_1  , - num_2  , - num_3 from numformat_ex;
insert into numformat_ex select  16+id, 5*num_1  , 5*num_2  , 5*num_3 from numformat_ex;

select num_1, num_2, num_3 from numformat_ex order by id;

         1          1          1
        11         11         11
       111        111        111
      1111       1111       1111
     11111      11111      11111
       1.1          1        1.1
      1.11          1       1.11
     1.111          1       1.11
        -1         -1         -1
       -11        -11        -11
      -111       -111       -111
     -1111      -1111      -1111
    -11111     -11111     -11111
      -1.1         -1       -1.1
     -1.11         -1      -1.11
    -1.111         -1      -1.11
         5          5          5
        55         55         55
       555        555        555
      5555       5555       5555
     55555      55555      55555
       5.5          5        5.5
      5.55          5       5.55
     5.555          5       5.55
        -5         -5         -5
       -55        -55        -55
      -555       -555       -555
     -5555      -5555      -5555
    -55555     -55555     -55555
      -5.5         -5       -5.5
     -5.55         -5      -5.55
    -5.555         -5      -5.55

set numf 999999.99999
select num_1, num_2, num_3 from numformat_ex order by id;

      1.00000       1.00000       1.00000
     11.00000      11.00000      11.00000
    111.00000     111.00000     111.00000
   1111.00000    1111.00000    1111.00000
  11111.00000   11111.00000   11111.00000
      1.10000       1.00000       1.10000
      1.11000       1.00000       1.11000
      1.11100       1.00000       1.11000
     -1.00000      -1.00000      -1.00000
    -11.00000     -11.00000     -11.00000
   -111.00000    -111.00000    -111.00000
  -1111.00000   -1111.00000   -1111.00000
 -11111.00000  -11111.00000  -11111.00000
     -1.10000      -1.00000      -1.10000
     -1.11000      -1.00000      -1.11000
     -1.11100      -1.00000      -1.11000
      5.00000       5.00000       5.00000
     55.00000      55.00000      55.00000
    555.00000     555.00000     555.00000
   5555.00000    5555.00000    5555.00000
  55555.00000   55555.00000   55555.00000
      5.50000       5.00000       5.50000
      5.55000       5.00000       5.55000
      5.55500       5.00000       5.55000
     -5.00000      -5.00000      -5.00000
    -55.00000     -55.00000     -55.00000
   -555.00000    -555.00000    -555.00000
  -5555.00000   -5555.00000   -5555.00000
 -55555.00000  -55555.00000  -55555.00000
     -5.50000      -5.00000      -5.50000
     -5.55000      -5.00000      -5.55000
     -5.55500      -5.00000      -5.55000

set numf 999999.9
select num_1, num_2, num_3 from numformat_ex order by id;

      1.0       1.0       1.0
     11.0      11.0      11.0
    111.0     111.0     111.0
   1111.0    1111.0    1111.0
  11111.0   11111.0   11111.0
      1.1       1.0       1.1
      1.1       1.0       1.1
      1.1       1.0       1.1
     -1.0      -1.0      -1.0
    -11.0     -11.0     -11.0
   -111.0    -111.0    -111.0
  -1111.0   -1111.0   -1111.0
 -11111.0  -11111.0  -11111.0
     -1.1      -1.0      -1.1
     -1.1      -1.0      -1.1
     -1.1      -1.0      -1.1
      5.0       5.0       5.0
     55.0      55.0      55.0
    555.0     555.0     555.0
   5555.0    5555.0    5555.0
  55555.0   55555.0   55555.0
      5.5       5.0       5.5
      5.6       5.0       5.6
      5.6       5.0       5.6
     -5.0      -5.0      -5.0
    -55.0     -55.0     -55.0
   -555.0    -555.0    -555.0
  -5555.0   -5555.0   -5555.0
 -55555.0  -55555.0  -55555.0
     -5.5      -5.0      -5.5
     -5.6      -5.0      -5.6
     -5.6      -5.0      -5.6

set numf 999.00000
select num_1, num_2, num_3 from numformat_ex order by id;

   1.00000    1.00000    1.00000
  11.00000   11.00000   11.00000
 111.00000  111.00000  111.00000
########## ########## ##########
########## ########## ##########
   1.10000    1.00000    1.10000
   1.11000    1.00000    1.11000
   1.11100    1.00000    1.11000
  -1.00000   -1.00000   -1.00000
 -11.00000  -11.00000  -11.00000
-111.00000 -111.00000 -111.00000
########## ########## ##########
########## ########## ##########
  -1.10000   -1.00000   -1.10000
  -1.11000   -1.00000   -1.11000
  -1.11100   -1.00000   -1.11000
   5.00000    5.00000    5.00000
  55.00000   55.00000   55.00000
 555.00000  555.00000  555.00000
########## ########## ##########
########## ########## ##########
   5.50000    5.00000    5.50000
   5.55000    5.00000    5.55000
   5.55500    5.00000    5.55000
  -5.00000   -5.00000   -5.00000
 -55.00000  -55.00000  -55.00000
-555.00000 -555.00000 -555.00000
########## ########## ##########
########## ########## ##########
  -5.50000   -5.00000   -5.50000
  -5.55000   -5.00000   -5.55000
  -5.55500   -5.00000   -5.55000

set numf 00000.99999
select num_1, num_2, num_3 from numformat_ex order by id;

 00001.00000  00001.00000  00001.00000
 00011.00000  00011.00000  00011.00000
 00111.00000  00111.00000  00111.00000
 01111.00000  01111.00000  01111.00000
 11111.00000  11111.00000  11111.00000
 00001.10000  00001.00000  00001.10000
 00001.11000  00001.00000  00001.11000
 00001.11100  00001.00000  00001.11000
-00001.00000 -00001.00000 -00001.00000
-00011.00000 -00011.00000 -00011.00000
-00111.00000 -00111.00000 -00111.00000
-01111.00000 -01111.00000 -01111.00000
-11111.00000 -11111.00000 -11111.00000
-00001.10000 -00001.00000 -00001.10000
-00001.11000 -00001.00000 -00001.11000
-00001.11100 -00001.00000 -00001.11000
 00005.00000  00005.00000  00005.00000
 00055.00000  00055.00000  00055.00000
 00555.00000  00555.00000  00555.00000
 05555.00000  05555.00000  05555.00000
 55555.00000  55555.00000  55555.00000
 00005.50000  00005.00000  00005.50000
 00005.55000  00005.00000  00005.55000
 00005.55500  00005.00000  00005.55000
-00005.00000 -00005.00000 -00005.00000
-00055.00000 -00055.00000 -00055.00000
-00555.00000 -00555.00000 -00555.00000
-05555.00000 -05555.00000 -05555.00000
-55555.00000 -55555.00000 -55555.00000
-00005.50000 -00005.00000 -00005.50000
-00005.55000 -00005.00000 -00005.55000
-00005.55500 -00005.00000 -00005.55000

Links

See also set numwidth and column format.