4 solutions to Oracle decimal point retention problems

The following article focuses on the actual retention of the Oracle decimal point. If you have requirements for extracting the Business list, we need to use the percentage. Normally, we need to keep two decimal places for it. In fact, we can only use round (_ data, 2), but the format is not neat.

If you do not have strict format requirements, you can use round. The following are the solutions you can find on the Internet:

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 decimal points on the left side of Oracle in the format must be enough; otherwise, the number to be queried will be 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 Oracle in the format must be known. Otherwise, the number exceeding the number is displayed.

Another problem is whether the setting takes effect at the session or system level when column is used.

Maybe the numeric column of a table does not always require that the data be displayed in the format of the last two decimal places in Oracle,

At this time, you can only use the session level, but there is a database connection session Timeout problem. If you do not use the system level, 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 decimal places on the left side of Oracle in the format of 9 or 0 is enough, 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 method after method 1 is remedied. It is best to use the Oracle decimal point n 9, 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 retention of decimal points in Oracle. I hope you will have some gains.