Good way to format decimal in SQL Server

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

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