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

Number format elements [Oracle SQL]

create table some_numbers (
  n1 number,
  n2 number,
  n3 number,
  n4 number,
  n5 number,
  n6 number,
  n7 number
);

create table some_formats (f varchar2(25));
insert into some_numbers values (
         0,
         4,
        -9,
       492,
     -4192,
     93764.19034,
     -7284.37829
);
insert into some_formats values (  '9,999');
insert into some_formats values ('999,999');
insert into some_formats values ('999.999');
insert into some_formats values ( '$99999');
insert into some_formats values ('0009999');
insert into some_formats values ('9990000');
insert into some_formats values ('0099900');
insert into some_formats values (  'B9999');
insert into some_formats values ('C999999');
insert into some_formats values ('9999D99');
insert into some_formats values ('9.9EEEE');
insert into some_formats values ('99.99EEEE');
insert into some_formats values ('999G999');
insert into some_formats values ('999G999D999');
insert into some_formats values ('L999');
insert into some_formats values ('99999MI');
insert into some_formats values ('99999PR');
insert into some_formats values ('RN');
insert into some_formats values ('rn');
insert into some_formats values ('S9999');
insert into some_formats values ('TM9');
insert into some_formats values ('TMe');
insert into some_formats values ('TMe');
insert into some_formats values ('U99999');
insert into some_formats values ('XXXXXX');
column f  format a15
column n1 format a15
column n2 format a15
column n3 format a15
column n4 format a15
column n5 format a15
column n6 format a18
column n7 format a19

set linesize 150
set pagesize 100
alter session set nls_currency='CHF';
select
  null, 
  to_char(n1) n1, 
  to_char(n2) n2, 
  to_char(n3) n3, 
  to_char(n4) n4, 
  to_char(n5) n5, 
  to_char(n6) n6, 
  to_char(n7) n7
from
  some_numbers
union all
select
  f,
  to_char( n1, f) n1,
  to_char( n2, f) n2,
  to_char( n3, f) n3,
  to_char( n4, f) n4,
  to_char( n5, f) n5,
  to_char( n6, f) n6,
  to_char( n7, f) n7
from
  some_numbers cross join some_formats;
NULL                      N1              N2              N3              N4              N5              N6                 N7                       
------------------------- --------------- --------------- --------------- --------------- --------------- ------------------ -------------------      
                          0               4               -9              492             -4192           93764.19034        -7284.37829              
9,999                          0               4              -9             492          -4,192          ######             -7,284                   
999,999                          0               4              -9             492          -4,192          93,764             -7,284                 
999.999                       .000           4.000          -9.000         492.000        ########        ########           ########                 
$99999                         $0              $4             -$9            $492          -$4192          $93764             -$7284                  
0009999                    0000000         0000004        -0000009         0000492        -0004192         0093764           -0007284                 
9990000                       0000            0004           -0009            0492           -4192           93764              -7284                 
0099900                    0000000         0000004        -0000009         0000492        -0004192         0093764           -0007284                 
B9999                                         4              -9             492           -4192           #####              -7284                    
C999999                             USD0            USD4           -USD9          USD492        -USD4192        USD93764           -USD7284           
9999D99                        .00            4.00           -9.00          492.00        -4192.00        ########           -7284.38                 
9.9EEEE                      .0E+00         4.0E+00        -9.0E+00         4.9E+02        -4.2E+03         9.4E+04           -7.3E+03                
99.99EEEE                    .00E+00        4.00E+00       -9.00E+00        4.92E+02       -4.19E+03        9.38E+04          -7.28E+03               
999G999                          0               4              -9             492          -4,192          93,764             -7,284                 
999G999D999                       .000           4.000          -9.000         492.000      -4,192.000      93,764.190         -7,284.378             
L999                                CHF0            CHF4           -CHF9          CHF492  ##############  ##############     ##############           
99999MI                       0               4               9-            492            4192-          93764               7284-                   
99999PR                        0               4              <9>            492           <4192>          93764              <7284>                  
RN                        ###############              IV ###############          CDXCII ############### ###############    ###############          
rn                        ###############              iv ###############          cdxcii ############### ###############    ###############          
S9999                        +0              +4              -9            +492           -4192           #####              -7284                    
TM9                       0               4               -9              492             -4192           93764.19034        -7284.37829              
TMe                       0E+00           4E+00           -9E+00          4.92E+02        -4.192E+03      9.376419034E+04    -7.28437829E+03          
TMe                       0E+00           4E+00           -9E+00          4.92E+02        -4.192E+03      9.376419034E+04    -7.28437829E+03          
U99999                                  $               $              -$             $49           -$419           $93764             -$7284         
                          0               4               9               2               2                                                           
                                                                                                                                                      
XXXXXX                          0               4         #######             1EC         #######           16E44            #######                  
Cleaning up...
drop table some_numbers;
drop table some_formats;