CODEWITHSUNDEEP
sqloracle-database
loalexzzzz
loalexzzzz

Reputation: 493

Oracle : Format the decimal number for display

I want to display a list of decimal number with using to_char function in oracle, but I don't know how to do..

SELECT REGEXP_REPLACE(TO_CHAR(-11111.1,'fm000000000D999999999'),'^\.','0.')  FROM dual;

Result:

-000011111.1

May I know how to print out the standard pattern of decimal list like below? You may expect the decimal number of range is '999999.999999'. Thanks.

Negative decimal number --------------------

-000011111.1  --> -11111.1
-0.1          --> -0.1
-0000.1       --> -0.1
-1.1000       --> -1.1


Positive decimal number ---------------------

000011111.1  --> 11111.1
0.1          --> 0.1
0000.1       --> 0.1
1.1000       --> 1.1

Upvotes: 1

Views: 3737

Answers (2)

Anton Zaviriukhin
Anton Zaviriukhin

Reputation: 741

SELECT rtrim(TO_CHAR(NUM,'99999999990D9999999999'),'0.')  FROM 
(SELECT -000011111.1 AS NUM FROM dual UNION ALL
 SELECT -0.1         AS NUM FROM dual UNION ALL
 SELECT -0000.1      AS NUM FROM dual UNION ALL
 SELECT -1.1000      AS NUM FROM dual UNION ALL
 SELECT 000011111.1  AS NUM FROM dual UNION ALL
 SELECT 0.1          AS NUM FROM dual UNION ALL
 SELECT 1            AS NUM FROM dual UNION ALL
 SELECT 0000.1       AS NUM FROM dual UNION ALL
 SELECT 1.1000       AS NUM FROM dual);

Upvotes: 1

arminrock
arminrock

Reputation: 535

I am using this kind of formatting with to_char and had no problem with it yet.
'FM99999999999999990.0'
The key is to add a zero before the point.

Hope this helps.

Upvotes: 3

Related Questions