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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

SELECT cast  cast round 37 0 38 0 2  AS DECIMAL 18 2   as varchar 100           RESULT   0 97

User · Answer

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

User · Answer

And for all SQL Server versions  SELECT CAST 0 973684210526315789   100 AS DECIMAL 18  2

[sql-server] Format number as percent in MS SQL Server

Examples related to sql-server

Examples related to tsql

Examples related to percentage