T-SQL: formatting "numeric" to format similar to c# style {0:#.00####}

Question

Is there an easy way to format SQL Server numeric(18,6) data type to format similar to c#'s {0:#.00####} but in T-SQL?

E.g.

1. 12.550000 should produce 12.55.
2. 14.456700 should produce 14.4567.
3. 15.00 should produce 15.00.

Answer 1

There's the CONVERT() and CAST() functions

SELECT CAST(@val AS numeric(18,6))

SELECT CAST(@val AS decimal(18,6))

I haven't come across a native T-SQL format string, but for 2005 onwards, I'm sure you could implement something with CLR UDF's written in .NET language of your choice.

Answer 2

Frankly that type of formatting is best done by the application not in the SQL. SQl is for data access and for affecting the content of the data, it is not really good at any formatting.

Answer 3

As HLGEM says, it's best to do this kind of stuff on the front end, but for kicks, the following code seems to work. There's probably a better way to do it though.

REPLACE(
     RTRIM(
          REPLACE(
               CAST(@test AS VARCHAR), '0', ' '
          )
     ), ' ', '0'
) +
CASE
     WHEN @test = FLOOR(@test) THEN '00'
     WHEN FLOOR(@test*10) = @test * 10 THEN '0'
     ELSE ''
END

Answer 4

A little bit different case without trailing '.00':

CAST(CAST(@val AS REAL) AS VARCHAR)

1. 12.550000 will produce 12.55.
2. 14.456700 will produce 14.4567.
3. 15.00 will produce 15.