Format number with comma and 1 decimal point

Question

• How can I format a number like this 1,234.5 even if the number is 1,234.50 or 1,234.56 ?
Thursday, December 26, 2013 9:49 PM

• I am not sure I understand your question.

If your problem is only with the second decimal digit, then you can use ROUND to avoid rounding, and CAST to transform it to a data type with fewer decimals.

```SELECT ROUND(1234.56, 1, 1)
,      CAST(ROUND(1234.56, 1, 1) AS decimal(5,1))

```

If your problem is with the formatting, to get the ","  and "."  in the right places, then please show the code that currently formats the number.

Gert-Jan

Friday, December 27, 2013 12:19 PM

All replies

• Please, please, please read any book on RDBMS and modern programming! We use tiered architectures like SQL's Client/Server.Display formatting is always done in a presentation layer and never, never in the database. This is usually covered in the first programming or software engineering course.

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Thursday, December 26, 2013 10:09 PM
• As Celko said, it is best to do most output formatting in the front end, not SQL.  But if you want to do it in SQL and you are on SQL 2012 or later, you can use the FORMAT function.

```declare @test numeric(8,3);
set @test = 1234.567;
select Format(Round(@test, 1, 1), '#,###,###.#');```

Tom

Thursday, December 26, 2013 10:16 PM
• Please, please, please read any book on RDBMS and modern programming! We use tiered architectures like SQL's Client/Server.Display formatting is always done in a presentation layer and never, never in the database. This is usually covered in the first programming or software engineering course.

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

I am not the developer of the front end and it is not possible to do what I need to do in the front end so I NEED, NEED, NEED to do it in SQL
Thursday, December 26, 2013 11:08 PM
• As Celko said, it is best to do most output formatting in the front end, not SQL.  But if you want to do it in SQL and you are on SQL 2012 or later, you can use the FORMAT function.

```declare @test numeric(8,3);
set @test = 1234.567;
select Format(Round(@test, 1, 1), '#,###,###.#');```

Tom

We are using SQL 2008 not 2012
Thursday, December 26, 2013 11:08 PM
• In that case, the best alternative is probably to write a CLR function and use format this way.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Thursday, December 26, 2013 11:21 PM
• As Erland said, CLR is best.  If you must do it in pure SQL, one way would be to round it down, convert to money, then to varchar with style 1, for example

```declare @test numeric(8,3);
set @test = 1234.567;
with cte as
(select Convert(varchar(20), Convert(money, Round(@test, 1, 1)), 1) as stringtest)
select Substring(stringtest, 1, Len(stringtest) - 1) from cte;```
Tom

Thursday, December 26, 2013 11:53 PM
• I am not sure I understand your question.

If your problem is only with the second decimal digit, then you can use ROUND to avoid rounding, and CAST to transform it to a data type with fewer decimals.

```SELECT ROUND(1234.56, 1, 1)
,      CAST(ROUND(1234.56, 1, 1) AS decimal(5,1))

```

If your problem is with the formatting, to get the ","  and "."  in the right places, then please show the code that currently formats the number.

Gert-Jan

Friday, December 27, 2013 12:19 PM
• ` Format(Round(@test, 1, 1), '#,###,###.#');`
It is funny to see this template string function again. It was first proposed for Algol 60, which had no I/O in is specification. I think Knuth wrote the article, bu it was one of the famous comp sci guys of the day . Later, the function was picked up by some versions of BASIC in the 1970s.

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Friday, December 27, 2013 3:07 PM