Wicked Number Formatting in T-SQL
One thing you are actually not supposed to do is to format your data in the database. Databases are to store and manage data, and presentation is for the UI layer.
Life has its funny ways to make you do things which you should not under normal cirumstances, things which you have sworn by yourself not to. I came to a situation where I needed to format a money number following “#,.####” within the body of a T-SQL stored procedure. I simply had to do it there. The requirement came in a very late moment of the testing phase, when moving this part out was definitely not feasible, considering the approaching deadline.
T-SQL has some limited functions to format data into string. In reference to numbers - float and money, you can use CONVERT. For float, there is no sensible option for ‘#,.####‘. For money, CONVERT gives three options:
| 0 (default) | No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98. |
| 1 | Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92. |
| 2 | No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point; for example, 4235.9819. |
As you can see, ‘#,.####’ is a mix of 1 and 2.
Poor man is devil, Bulgarians say. What I did was to get the string, result of CONVERT with style 1, and concatenate it with the last two characters of the string, result of CONVERT with style 2:
| UPDATE : 14th December 2009 |
There is a bug in the above code, which produces wrong result in case the last two digits are greater than 49. The correct solution would be:
Tags: Format numbers, SQL Server, T-SQL
May 22nd, 2010 at 9:15 am
Very nice blog)