Ian Henry Ian Henry - 4 months ago 8
SQL Question

Is there any simple way to format decimals in T-SQL?

I know it could be done trivially in a non-SQL environment [post-data processing, frontend, what have you], but that's not possible at the moment. Is there a way to take a

decimal(5,2)
and convert it to a
varchar
without the trailing zeroes/decimal points? For example:

declare @number decimal(5,2)
set @number = 123.00
select cast(@number as varchar) as FormattedNumber


And the result is '123.00'. Is there a (simple) way to get '123' instead? And likewise, instead of '123.30', '123.3'? Could do it by figuring out whether or not the hundredths/tenths places were 0 and manually trimming characters, but I wanted to know if there was a more elegant solution.

Answer

What about:

SELECT CAST(CAST(@number AS float) AS varchar(10))

However you may want to test this carefully with your raw data first.