Four Solutions to Oracle decimal point retention problems

If you need to retain the Oracle decimal point in practical applications, the actual method is very simple. We only use round (_ data, 2 )), however, if the format is not neat, you can use the round. The following is a solution for searching through the network:

Method 1: Use the fm format of to_char, that is:

 
 

  
  
to_char(round(data.amount,2),'FM9999999999999999.00') as amount 
 
 

The disadvantage is that if the value is 0, it is displayed as. 00 instead of 0.00.

Note that the number of digits on the left of the decimal point must be enough in the format. Otherwise, the number to be queried is displayed as n symbols "#".

The solution is as follows:

 
 

  
  
select decode(salary,0,'0.00',
(to_char(round(salary,2),'fm99999999999999.00'))) from can_do; 
 
 

Method 2: Use case when then else end to determine and handle various situations:

 
 

  
  
case  
  
  
when instr(to_char(data.amount), '.') < 1 then  
  
  
data.amount || '.00'  
  
  
when instr(to_char(data.amount), '.') + 1 = length(data.amount) then  
  
  
data.amount || '0'  
  
  
else  
  
  
to_char(round(data.amount, 2))  
  
  
end as amount_format  
 
 

Method 3: You can use the parameter settings provided by Oracle, that is

 
 

  
  
column amount format l9999999999.99 
 
 

The disadvantage of this method is that the number of 9 decimal points on the left of the format must be known; otherwise, the number exceeding the number is displayed.

Another Oracle decimal point retention problem is that when column is used, the setting takes effect at the session level or system level. Pay attention to this.

Maybe the numeric column of a table does not always require the display of all places, it is in the format of two digits after the decimal point,

At this time, you can only use the session level, but there is an Oracle decimal point reservation problem when the database connection session times out. If not, this method is not recommended.

Method 4: Use to_char + trim as follows:

 
 

  
  
select trim(to_char(1234,'99999999999999.99')) from dual; 
 
 

Or

 
 

  
  
select ltrim(trim(to_char(1234.525,'00000000000000.00')),'0') from dual; 
 
 

14 9 or 14 0 formats are used here. We recommend that you use 14 9 for convenience. The disadvantage of Method 4 is that

If the value is 0, it is converted to. 00 instead of 0.00. The remedy is to decode it.

Note that the number of digits to the left of the decimal point is enough in the format, and the number to be queried is displayed as n symbols "#".

As follows:

 
 

  
  
select decode(salary,0,'0.00',trim(to_char(salary,'99999999999999.99'))) from can_do; 
 
 

Or

 
 

  
  
select decode(salary,0,'0.00',
ltrim(trim(to_char(salary,'00000000000000.00')),'0')) from can_do; 
 
 

CONCLUSION: The trim + to_char method in Method 4 is recommended or the remedy method after method 1 is used, and it is best to use the method with n digits 9 on the left of the decimal point instead of 0. Otherwise, trim processing is required.

That is:

 
 

  
  
select decode(salary,0,'0.00',
trim(to_char(salary,'99999999999999.99'))) from can_do; 
 
 

Or

 
 

  
  
select decode(salary,0,'0.00',
(to_char(round(salary,2),'fm99999999999999.00'))) from can_do;  
 
 

The above content is an introduction to the Oracle decimal point retention issue. I hope you will get some benefits.