Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Simple SQL question
fm in the format mask is a useful tool for removing the leading space
reserved for the minus sign, and in other situations. I created a
little test, and found it also removes trailing zeros following a
decimal point.
The following test would look better if I could format my message to fixed-spaced text:
SQL> Declare
2 procedure Tst( n in number, fmt in varchar2) is begin
3 dbms_output.put_line 4 (lpad(to_char(n),8) || lpad(fmt,10) 5 || ' |' || to_char(n,fmt) ||'|' );6 end Tst;
8 Tst(0, '999'); 9 Tst(0, 'fm999'); 10 Tst(0, '000'); 11 Tst(0, 'fm000'); 12 Tst(-123, '999'); 13 Tst(-123, 'fm999'); 14 Tst(123, '999'); 15 Tst(123, 'fm999'); 16 Tst(1.2, '999.99'); 17 Tst(1.2, 'fm999.99'); 18 Tst(-1.2, '990.00'); 19 Tst(-1.2, 'fm990.00'); 20 Tst(1.2, '990.099'); 21 Tst(1.2, 'fm990.099'); 22 Tst(1.2, '000.000'); 23 Tst(1.2, 'fm000.000');
0 999 | 0| 0 fm999 |0| 0 000 | 000| 0 fm000 |000|Received on Thu Feb 02 2006 - 15:13:18 CST
-123 999 |-123|
-123 fm999 |-123|
123 999 | 123| 123 fm999 |123| 1.2 999.99 | 1.20| 1.2 fm999.99 |1.2|
-1.2 990.00 | -1.20|
-1.2 fm990.00 |-1.20|
1.2 990.099 | 1.200| 1.2 fm990.099 |1.2| 1.2 000.000 | 001.200| 1.2 fm000.000 |001.200| PL/SQL procedure successfully completed.