CODEWITHSUNDEEP
sqlsql-servert-sqlformattingdecimal
Brad
Brad

Reputation: 1415

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.

Upvotes: 3

Views: 10945

Answers (3)

Cade Roux
Cade Roux

Reputation: 89741

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

Upvotes: 2

AdaTheDev
AdaTheDev

Reputation: 147354

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.

Upvotes: 0

Gabriele Petrioli
Gabriele Petrioli

Reputation: 196246

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

Upvotes: 2

Related Questions