Good way to format decimal in SQL Server
- by Brad
We store a decimal(9,8) in our database. It can have any number of places after the decimal point (well, no more than 8). I am frustrated because I want to display it as human-readable text as part of a larger string created on the server. I want as many decimals to the right of the decimal point as are non-zero, for example:
0.05
0.12345
3.14159265
Are all good
If I do
CAST(d AS varchar(50))
I get formatting like:
0.05000000
0.12345000
3.14159265
I get similar output if I cast/convert to a float or other type before casting to a varchar. I know how to do a fixed number of decimal places, such as:
0.050
0.123
3.142
But that is not what I want.
Yes, I know I can do this through complicated string manipulation (REPLACE, etc), there should be a good way to do it.
