Output formatting - ORA SQL - TO_CHAR(fm00.00)

Question

I am trying to format output with consistent decimal places. I know numbers and decimals in ORA SQL are similar. I am having an issue with the output of to_char being evaluated after round.

--drop table test;

Create table test as
SELECT '10' as Total,'2' as Other, '3' as Other1 FROM DUAL
UNION ALL (SELECT '9.5', '9.55', '9.96509130256277' FROM DUAL)
UNION ALL (SELECT '9.5555', '9.55555', '119.9' FROM DUAL)
UNION ALL (SELECT '1000', '9999', '9.456789' FROM DUAL);



select 
Case    
   when (Other1 >=10 and Other1 < 100) then to_char(round(Other1, 1), 'fm00.0')||'%'
   when (Other1 < 10) then to_char(round(Other1, 1), 'fm0.0')||'%'
   when (Other1 >=100) then to_char(round(Other1, 1), 'fm000.0')||'%'
   
   else '5' END Other2
from test

I get:

OTHER2
3.0%
####%
119.9%
9.5%

I want to get:

OTHER2
3.0%
10.0%
119.9%
9.5%

Answer 1

Use the format model FM990.0 to have two optional digits then a required unit digit then the decimal separator and then a single decimal digit (you can also use D instead of . for a localised decimal separator):

SELECT TO_CHAR(ROUND(other1, 1), 'FM990.0') || '%' AS other2
FROM   test

or, TO_CHAR will automatically round so you can simplify it to:

SELECT TO_CHAR(other1, 'FM990.0') || '%' AS other2
FROM   test

Which, for the sample data:

CREATE TABLE test (OTHER1) AS
SELECT   2.95 FROM DUAL UNION ALL
SELECT   9.97 FROM DUAL UNION ALL
SELECT 119.93 FROM DUAL UNION ALL
SELECT   9.5  FROM DUAL;

Both output:

OTHER2
3.0%
10.0%
119.9%
9.5%

fiddle