Numbers formatting in SQL

There is no simple way to format numbers adding thousand separators (commas) to large numbers to make them easier to view in SQL for an int, floats or any other numeric data type.

Workaround:

  1. Use CONVERT function to convert the number to MONEY type
  2. Use CONVERT function to convert MONEY to a VARCHAR with a style of 1, which adds the commas, decimal point and two decimal places
  3. Use REPLACE to strip decimal points and numbers off

 

DECLARE @MyNumber BIGINT
 SET @MyNumber = 111222333444
 
 SELECT REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,@BigNumber),1), '.00','')

The result is :

111,222,333,444

 

However we still have a problem when 1 (one) decimal number after the decimal point is required. That is where I found a script to create a function to format any number to the custom format. The function takes two parameters,

  1. Number to be formatted,  ex : (123456789.99999)
  2. Required Format, ex : (99,99,99,999.99)

It returns formatted number as output ex : (12,34,56,789.99)

http://itdeveloperzone.blogspot.com.au/2011/03/format-number-in-sql.html

My conversion now looks like this:

pf=dbo.fnFormatNumber(Prcnt_Free, '99,999.9')

 

Ideally all numbers formatting should be done in the presentation layer like Reporting Services or Excel rather than in SQL. My requirement was different as I had a script which dynamically generated HTML page, hence I formatted numbers in SQL script.

1 comment for “Numbers formatting in SQL

  1. February 8, 2016 at 8:58 am

    Thanks for sharing my post. Hope it works fine…

Leave a Reply

Your email address will not be published. Required fields are marked *