3

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%.

trincot
  • 317,000
  • 35
  • 244
  • 286
karthik adiga
  • 133
  • 2
  • 12

1 Answers1

3

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'.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • Hi, have one more doubt,If I have to display the same thing for a column having number datatype (removing the appending of '%' part in the above formaula),what should be the datatype and formula,i tried decimal (5,2) and decimal (5,4) but when I remove the tochar format mentioned in the above solution and tried various formats but it is not giving the solution like above ,pl help – karthik adiga Feb 14 '19 at 05:41
  • @karthikadiga trying to explain as far as I understoood, whenever is `8.5`, you need `'fm990D00'` to convert to `8.50`, `'fm990D0000'` to convert to `8.5000`. That is, you need `to_char` for conversion, otherwise it stands as `8.5`. – Barbaros Özhan Feb 14 '19 at 06:00
  • @karthikadiga hm OK, you can declare `perc` as `number(10,2)` or `number` or `float`. – Barbaros Özhan Feb 14 '19 at 11:33