Even though Oracle Database has a TO_CHAR(number) function that allows us to format numbers, it doesn’t provide a format element for the percentage sign.

Therefore, if we want to format a number as a percentage in Oracle Database, we need to concatenate the percentage sign and the number.

Example

We can use the CONCAT() function to concatenate the number and the percentage sign.

We can still use the TO_CHAR(number) function to format the number so that it has the desired decimal places, leading zeros (or not), etc:

SELECT CONCAT(TO_CHAR(18, 'fm00D00'), '%')
FROM DUAL;

Result:

18.00%

Here, I used the 0 format element, which means that the digit position will always be printed, even if it contains a leading/trailing zero.

I also used the fm format modifier to suppress any leading/trailing zeros or blanks.

Here it is with some other format models:

SELECT 
    CONCAT(TO_CHAR(7, 'fm00'), '%') AS "1",
    CONCAT(TO_CHAR(7, 'fm99'), '%') AS "2",
    CONCAT(TO_CHAR(7.4567, 'fm0D00'), '%') AS "3",
    CONCAT(TO_CHAR(7, 'fm0D00'), '%') AS "4"
FROM DUAL;

Result:

     1     2        3        4 
______ _____ ________ ________ 
07%    7%    7.46%    7.00%   

We can perform a calculation against the number if required:

SELECT 
    CONCAT(TO_CHAR(18 * 0.1, 'fm99D00'), '%') AS "1",
    CONCAT(TO_CHAR(0.18 * 100, 'fm99D00'), '%') AS "2"
FROM DUAL;

Result:

       1         2 
________ _________ 
1.80%    18.00%   

In this case I included the 9 format element so that any leading zeros were omitted.

Omitting the TO_CHAR() Function

If we have no specific need to format the number other than to convert it to a percentage format, then we don’t even need the TO_CHAR() function:

SELECT CONCAT(18, '%')
FROM DUAL;

Result:

18%

The Concatenation Operator

Another way to concatenate the number and the percentage sign is to use the concatenation operator (||):

SELECT 18 || '%'
FROM DUAL;

Result:

18%

And here it is with the TO_CHAR() function added for extra formatting:

SELECT TO_CHAR(18, 'fm00D00') || '%'
FROM DUAL;

Result:

18.00%