tsql – Format number as percent in MS SQL Server

tsql – Format number as percent in MS SQL Server

In SQL Server 2012 and later, there is the FORMAT() function. You can pass it a P parameter for percentage. For example:

SELECT FORMAT((37.0/38.0),P) as [Percentage] -- 97.37 %

To support percentage decimal precision, you can use P0 for no decimals (whole-numbers) or P3 for 3 decimals (97.368%).

SELECT FORMAT((37.0/38.0),P0) as [WholeNumberPercentage] -- 97 %
SELECT FORMAT((37.0/38.0),P3) as [ThreeDecimalsPercentage] -- 97.368 %

M.Alis answer could be modified as

select Cast(Cast((37.0/38.0)*100 as decimal(18,2)) as varchar(5)) +  % as Percentage

tsql – Format number as percent in MS SQL Server

Using FORMAT function in new versions of SQL Server is much simpler and allows much more control:

FORMAT(yournumber, #,##0.0%)

Benefit of this is you can control additional things like thousand separators and you dont get that space between the number and %.

Leave a Reply

Your email address will not be published.