Ian Henry Ian Henry - 3 months ago 6x
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

and convert it to a
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.


What about:

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

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