Search code examples
sqloracle-databaseplsqlpercentagesql-query-store

How to format percentage?

I need help in formatting percentages. My original reason for formatting is that I want to display percentages near zero as 0.05% and not as .05%

So I did this:

IF (a.TOTAL <> 0 AND b.mkt <> 0) THEN
    v_perc := TO_CHAR(ROUND(100-( a.TOTAL*100/ b.mkt),2),'00.99') || '%';
END IF;

v_perc is stored as varchar2(50), but the formatting is not always as I need it.

When the percentage is below 10%. I get like 08.52 or 00.35%. But I want a format where the output is like 0.52%, 5.32%, 55%, 0%, 100%. I tried with 000.999 but that will give 000.000%.

Solution

  • You can use

    TO_CHAR(<your_value>,'fm990D00','NLS_NUMERIC_CHARACTERS = ''.,''')

    as your formatting model.

    It satisfies your needs upto is 100( 100% ). Apart from percentage, if your need more digits in another case such as 11,115.54, you can convert the second argument to 'fm999G990D00'.