SQL Server Portal

In SQL Server, we usually come across a situation where we need to format values as a percentage. Usually, developers format the values as a percentage manually. But in this article, I will share a shortcut to format values as a percentage.
Before proceeding with the solution, I would like to create a sample table and data to demonstrate the formatting.

USE tempdb
GO
--DROP TABLE [dbo].[Student]
--GO
CREATE TABLE [dbo].[Student]
(
[Student ID] int,
[Student Name] [varchar](50) NOT NULL,
[Percentage] numeric(18,2)
)
GO
Insert into dbo.[Student] values (1,'Bob', 0.10)
Insert into dbo.[Student] values (2,'Alexander',0.50)
Insert into dbo.[Student] values (3,'Hosanna',0.85)
Insert into dbo.[Student] values (4,'William',0.11)
Insert into dbo.[Student] values (5,'Hulda',1.20)
Insert into dbo.[Student] values (6,'Jacoba',1.15)
GO

Solution 1 :
In this solution, we need to manually calculate the percentage and then format it. Given below is the script.

--This script is compatible with SQL Server 2000 and above.
USE tempdb
GO
SELECT [Student ID]
,[Student Name]
,[Percentage]
,CONVERT(VARCHAR(50),[Percentage]*100)+' %' AS [%] FROM dbo.Student
GO

Solution 2 :
In this solution, we will use a new function shipped with SQL Server 2012 namely Format. You just need to pass the values in this function and it will format values as a percentage (without any hurdles of calculations).
Given below is the script.

--This script is compatible with SQL Server 2012 and above.
SELECT [Student ID]
,[Student Name]
,[Percentage]
,FORMAT([Percentage],'p') AS [%] FROM dbo.Student
GO