CODEWITHSUNDEEP
oracle-database
Somnath Musib
Somnath Musib

Reputation: 3714

Unable to append append 0 in column value if value does not have a decimal point

I am generating a file from Oracle table where one of the column is of NUMBER data type.

The column definition is:

Amount NUMBER(13,2)

In this column, I have data out of which some with decimal point and some does not. 

e.g

1234.45
1245
1234.23

While getting the data, in case for values without having decimal point, I want the data to be appended with 00 (as scale is 2). For values with decimal it should be as it is.

e.g.

1234.45
1245.00
1234.23

How can I do that?

I can not simply pad with 0 as it is condition based. I am getting stuck at a point where I am not able to identify how to append 0s if there is a decimal point.

Upvotes: 0

Views: 239

Answers (2)

KAD
KAD

Reputation: 11102

You can convert it to characters and use the FM999999999.00 formatter:

SELECT TO_CHAR(your_column, 'FM999999999.00') AS RES FROM DUAL

The FM which is Fill mode. Allows variable-length number of digit as a subset of the number of digit that is provided before the decimal in the mask, so 999999999 = 9 digits, 8 digits will also be tolerated. But it will not accept 10 digits before the decimal.

The .00 represents the number of decimals after the point, in case you add more digits after the decimal, they will be rounded e.g. 100.246 ==> 100.25.

Format Model Modifiers Reference: https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#BABDAEDF

Upvotes: 2

Camille
Camille

Reputation: 2531

You can use to_char(col_name,'9999.00')

The 9s represent optional place holders. The 0s represent putting in leading zeros.

Upvotes: 2

Related Questions