Good way to format decimal in SQL Server
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.
Playing around (sql server) i find that casting to float first makes the trick ..
select cast( cast(0.0501000 as float) as varchar(50) )
yields
0.0501
Code copied almost verbatim from here (also discusses the 6-digit limit on float formatting in mode 0):
DECLARE @num3 TABLE (i decimal(9, 8))
INSERT @num3
SELECT 0.05
UNION ALL
SELECT 0.12345
UNION ALL
SELECT 3.14159265
SELECT i
,CASE WHEN PATINDEX('%[1-9]%', REVERSE(i)) < PATINDEX('%.%', REVERSE(i))
THEN LEFT(i, LEN(i) - PATINDEX('%[1-9]%', REVERSE(i)) + 1)
ELSE LEFT(i, LEN(i) - PATINDEX('%.%', REVERSE(i)))
END 'Converted'
FROM @num3
For anything other than fairly straight forward manipulation, I'd be considering doing this in your calling code instead tbh as I think it's usually best for SQL to return the data as-is from the database, and then leave the formatting of that up to whatever is calling it, which is more than likely better geared up for string manipulation. Especially if you find yourself jumping though hoops to try to achieve it.
精彩评论