SYSK 177: Formatting Numeric Data with Comma and Decimal Separators in SQL

  • Article

The other day I was asked whether it’s possible to format numeric data to have commas every three digits to the left of the decimal point, and two digits to the right of the decimal point using SQL Server, e.g. 5000.125 to 5,000.13.  The solution is quite straight forward – just use the convert function with style set to 1 as follows:

select convert(nvarchar(15), cast(5000.125 as money), 1)

 

Having said that, as a rule of thumb, I avoid doing formatting in SQL Server and prefer using UI or middle tier for that purpose for a simple reason that scaling out a database is significantly harder then scaling out web or application servers.