We have several options if we want to display numbers with a percentage sign in PostgreSQL.
We can use the TO_CHAR()
function to format the number along with the percentage sign. Or we can simply concatenate the number with the percentage sign, either with the CONCAT()
function or with the concatenation operator.
The TO_CHAR()
Function
This option involves passing the number and a numeric template pattern to the function to return the number formatted in the way specified by the template pattern. To get a percentage sign, we include it in our template pattern:
SELECT TO_CHAR(35, 'fm00D00%');
Result:
35.00%
Here, I used the 0
template pattern, 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 template patterns:
SELECT
TO_CHAR(7, 'fm00%') AS "1",
TO_CHAR(7, 'fm99%') AS "2",
TO_CHAR(7.4567, 'fm0D00%') AS "3",
TO_CHAR(7, 'fm0D00%') AS "4";
Result:
+-----+----+-------+-------+ | 1 | 2 | 3 | 4 | +-----+----+-------+-------+ | 07% | 7% | 7.46% | 7.00% | +-----+----+-------+-------+
We can perform a calculation against the number if required:
SELECT
TO_CHAR(35 * 0.1, 'fm99D00%') AS "1",
TO_CHAR(0.35 * 100, 'fm99D00%') AS "2";
Result:
+-------+--------+ | 1 | 2 | +-------+--------+ | 3.50% | 35.00% | +-------+--------+
In this case I include the 9
template pattern so that any leading zeros are omitted.
The CONCAT()
Function
Another way to do it is to use the CONCAT()
function to concatenate the number and the percentage sign:
SELECT CONCAT(35, '%');
Result:
35%
The Concatenation Operator
Another way to concatenate the number and the percentage sign is to use the concatenation operator (||
):
SELECT 35 || '%';
Result:
35%