Here are 4 ways to convert a number to a percentage value in SQL Server.
Strictly speaking, we’re not actually “converting” it to a percentage. We’re formatting the number as a percentage. But in order to do that, we need to convert the number from a numeric data type to a string.
Here are 4 ways to do that.
Example 1 – The FORMAT() Function
The most obvious choice to use is the FORMAT() function. This allows you to display numbers and dates in a specific format.
Here’s an example of using this function to display a number as a percentage:
SELECT FORMAT(55, 'P') Result;
Result:
+------------+ | Result | |------------| | 5,500.00 % | +------------+
Notice that four zeros were added to our value (two before the decimal point, and two after).
The following would be required to make this 55 percent:
SELECT FORMAT(.55, 'P') Result;
Result:
+----------+ | Result | |----------| | 55.00 % | +----------+
If the number is the actual percentage value that you want, you can do this:
SELECT FORMAT(55 * .01, 'P') Result;
Result:
+----------+ | Result | |----------| | 55.00 % | +----------+
You can also remove the fractional part by adding a zero to the format specifier:
SELECT FORMAT(.55, 'P0') Result;
Result:
+----------+ | Result | |----------| | 55 % | +----------+
If required, you can also add more decimal places:
SELECT FORMAT(.55123456, 'P7') Result;
Result:
+--------------+ | Result | |--------------| | 55.1234560 % | +--------------+
Example 2 – The CONVERT() Function
You can alternatively use the CONVERT() function to convert the number to a string, then add a percentage sign to the end.
This might seem a bit unnecessary given how easy the previous example made it, however, the FORMAT() function was only introduced in SQL Server 2012. Therefore, this is how you will need to do it if you use an earlier version of SQL Server.
SELECT CONVERT(VARCHAR(4), 55) + ' %' Result;
Result:
+----------+ | Result | |----------| | 55 % | +----------+
Of course, if your number is something like .55 and you need that to display as 55.00 %, then you can always multiply it by 100:
SELECT CONVERT(VARCHAR(6), 0.55 * 100) + ' %' Result;
Result:
+----------+ | Result | |----------| | 55.00 % | +----------+
In this case I also increased the size of the varchar data type to cater for the extra characters.
Also, you can remove the fractional part by using the LEFT() function:
SELECT CONVERT(VARCHAR(6), LEFT(0.55 * 100, 2)) + ' %' Result;
Result:
+----------+ | Result | |----------| | 55 % | +----------+
Although you’d need to be careful when doing this, as the actual value could vary to be more or less than 2. In which case, you could use the TRIM() function to trim leading zeros and/or trailing dots:
SELECT CONVERT(VARCHAR(6), TRIM('0,.' FROM LEFT(0.55 * 100, 3))) + ' %' Result;
Result:
+----------+ | Result | |----------| | 55 % | +----------+
However, this is not perfect, and the FORMAT() function obviously provides a lot more flexibility with a minimum of code.
Example 3 – The CAST() Function
We can alternatively use the CAST() function to do the same thing as the previous example:
SELECT CAST(55 AS VARCHAR(4)) + ' %' Result;
Result:
+----------+ | Result | |----------| | 55 % | +----------+
Note that CAST() and CONVERT() use slightly different syntaxes. In the case of CAST() the value to be cast comes first, whereas it’s the other way around with CONVERT().
Example 4 – The CONCAT() Function
You can also use the CONCAT() function to concatenate a number with the percentage sign:
SELECT CONCAT(55, ' %') Result;
Result:
+----------+ | Result | |----------| | 55 % | +----------+
This function implicitly converts all arguments to string types before concatenation.