Format number as percent in MS SQL Server

Question

I am trying to simply format a number as a percent with two decimal places. If it is 37 divided by 38 (aka .973684210526315789), I would like it to show 97.36 % in the SQL output. I know it is recommended to do formatting in the Application, however this is for an automated export. This is using SQL Server 2008.

Here is what I have now:

select CONVERT(VARCHAR(50),cast(37 as decimal)/cast(38 as decimal)*100)+' %' AS [%]

If you could explain what the various parameters are as well in any function that would be helpful.

score 146 · Answer 1 · edited Sep 20 '17 at 15:41

146

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 %

edited Sep 20 '17 at 15:41

SliverNinja - MSFT

31,051
11
110
173

answered May 06 '15 at 23:46

jbeldock

2,755
3
18
31

2

Sorry I should've mentioned I am using SQL Server 2008, so can't use that function. – user3513237 May 07 '15 at 15:31
Oh, well! In that case, you might find this article helpful, specifically Solution #1: http://raresql.com/2013/08/04/sql-server-how-to-format-values-as-a-percentage/ – jbeldock May 07 '15 at 15:38
3

what if we only want 97%. Only whole number % – Juan Velez Aug 10 '16 at 00:06
4

If you do have SQL Server 2008, the FORMAT function is really handy for this. Return a whole number by specifying precision like so: SELECT FORMAT(.99, 'P0'); – Nick Allan Jan 30 '17 at 11:34

score 50 · Accepted Answer · answered May 07 '15 at 10:11

50

M.Ali's answer could be modified as

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

answered May 07 '15 at 10:11

Sharad

793
8
14

2

This is the best answer because it works with any SQL version. Exactly what is needed. Thank you. – user3513237 May 07 '15 at 15:34
2

One slight correction to this one. Should be `as varchar(6)` or you won't allow for the edge case of 100.00% – iCollect.it Ltd May 17 '21 at 15:46

score 18 · Answer 3 · answered Mar 15 '19 at 00:23

18

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 don't get that space between the number and '%'.

answered Mar 15 '19 at 00:23

Simon Darlow

530
5
14

1

Helped me take out hte space. Thanks ! – Barak G. Oct 07 '21 at 13:14

score 6 · Answer 4 · answered May 06 '15 at 23:47

6

SELECT cast( cast(round(37.0/38.0,2) AS DECIMAL(18,2)) as varchar(100)) + ' %'

RESULT:  0.97 %

answered May 06 '15 at 23:47

M.Ali

67,945
13
101
127

3

The required answer is 97.36 % (not 0.97 %) – JohnS May 07 '15 at 03:00

score 3 · Answer 5 · answered May 07 '15 at 03:49

3

And for all SQL Server versions

SELECT CAST(0.973684210526315789 * 100 AS DECIMAL(18, 2))

answered May 07 '15 at 03:49

sqluser

5,502
7
36
50

Would have loved to use FORMAT(). But I am working with SQL 2008. This worked great. – SherlockSpreadsheets Jan 11 '19 at 22:12

RF1991 · Answer 6 · 2022-05-10T11:52:48.753

The issue that might rise is rounding issue.

.973684210526315789*100   -> 97.37  False
.973684210526315789*100   -> 97.36  True

there exist two approaches: String Functions and Numerical Functions

String Functions can avoid rounding issue, use functions such as CAST( equivalent CONVERT) and LEFT

SELECT LEFT(Cast(.973684210526315789*100 AS VARCHAR(100)), 5) + ' %'

SELECT LEFT(CONVERT(VARCHAR(100), .973684210526315789*100), 5) + ' %'

Numerical Functions might bring rounding issue, to avoid it use ROUND function

SELECT LEFT(Round(.973684210526315789*100, 2, 1), 5) + ' %'

to avoide using LEFT function(String Functions) use DECIMAL(18,2) and NUMERIC(18, 2) in cast and convert.

SELECT Concat(Cast(Round(.973684210526315789 * 100, 2, 1) AS DECIMAL(18, 2)),' %')

SELECT Concat(CONVERT(NUMERIC(10, 2), Round(.973684210526315789 * 100, 2, 1)),' %')

in addition you can use FORMAT, ###.## and 00.00 resemble integer part and fractional part and ###.## should be preferred to 00.00 since it can avoid zero values

SELECT FORMAT(Round(.973684210526315789*100, 2, 1), '######.#####')+ ' %' ---> 97.36%

SELECT FORMAT(Round(.973684210526315789*100, 2, 1), '00000.0000')+ ' %' ---> 00097.3600% 

SELECT FORMAT(Round(.973684210526315789*100, 2, 1), '00.00')+ ' %' ---> 97.36%

sqlfiddle

score -2 · Answer 7 · edited Dec 20 '19 at 18:58

-2

took the challenge to solve this issue. comment number 5 (M.Ali) asked for --"The required answer is 97.36 % (not 0.97 %) .." so for that i'm attaching a photo who compare between post number 3 (sqluser) to my solution. (i'm using the postgre_sql on a mac)

enter image description here

edited Dec 20 '19 at 18:58

SecretAgentMan

2,856
7
21
41

answered Dec 20 '19 at 18:44

Dor Budkov

1

Your answer may work for Postgres, but the syntax does not translate to MS Sql Server which is what was requested. – Rachel Ambler Dec 20 '19 at 20:24
you right about the to_char line, but if you try only the "Cast" line function it should work for you as you can see in the photo i loaded. – Dor Budkov Dec 28 '19 at 09:34

Format number as percent in MS SQL Server

7 Answers7

Linked

Related