CODEWITHSUNDEEP
sqloracle-database
y2j
y2j

Reputation: 207

how to add zeros after decimal in Oracle

I want to add zeroes after the number . for eg a= 6895
then a= 6895.00

datatype of a is number(12);

I am using the below code .

select to_char(6895,'0000.00')  from dual .

I m getting the desired result from above code but '6895' can be any number.so due to that i need to add '0' in above code manually. for eg.

select to_char(68955698,'00000000.00')  from dual .

Can any one suggest me the better method .

Upvotes: 2

Views: 16892

Answers (3)

Siva Rajesh
Siva Rajesh

Reputation: 1

With using CASE and SUBSTR it is very simple.

CASE WHEN SUBSTR(COLUMN_NAME,1,1) = '.' THEN '0'||COLUMN_NAME ELSE COLUMN_NAME END

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49122

datatype of a is number(12);

Then use 12 9s in the format model. And, keep the decimal to just 2. So, since the column datatype is NUMBER(12), you cannot have any number more than the given size.

SQL> WITH DATA AS(
  2  SELECT 12 num FROM dual union ALL
  3  SELECT 234 num FROM dual UNION ALL
  4  SELECT 9999 num FROM dual UNION ALL
  5  SELECT 123456789 num FROM dual)
  6  SELECT to_char(num,'999999999999D99') FROM DATA
  7  /

TO_CHAR(NUM,'999
----------------
           12.00
          234.00
         9999.00
    123456789.00

SQL>

Update Regarding leading spaces

SQL>  select ltrim(to_char(549,'999999999999.00')) from dual;

LTRIM(TO_CHAR(54
----------------
549.00

SQL>

Upvotes: 1

Ben
Ben

Reputation: 52923

The number format models are the place to start when converting numbers to characters. 0 prepends 0s, which means you'd have to get rid of them somehow. However, 9 means:

Returns value with the specified number of digits with a leading space if positive or with a leading minus if negative. Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed-point number.

So, the following gets you almost there, save for the leading space:

SQL> select to_char(987, '9999.00') from dual;

TO_CHAR(
--------
  987.00

You then need to use a format model modifier, FM, which is described thusly:

FM Fill mode. Oracle uses trailing blank characters and leading zeroes to fill format elements to a constant width. The width is equal to the display width of the largest element for the relevant format model

...

The FM modifier suppresses the above padding in the return value of the TO_CHAR function.

This gives you a format model of fm9999.00, which'll work:

SQL> select to_char(987, 'fm9999.00') from dual;

TO_CHAR(
--------
987.00

If you want a lot of digits before the decimal then simply add a lot of 9s.

Upvotes: 6

Related Questions