| René Nyffenegger's collection of things on the web | |
|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
|
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; |