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

Question

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 1

The Convert function may do what you want to do.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a87d0850-c670-4720-9ad5-6f5a22343ea8.htm

Answer 2

Let me try this again....

CREATE FUNCTION saneDecimal(@input decimal(5,2)) returns varchar(10)
AS
BEGIN
DECLARE @output varchar(10)
SET @output = CAST(@input AS varchar(10))

DECLARE @trimmable table (trimval char(1))
INSERT @trimmable VALUES ('0')
INSERT @trimmable VALUES ('.')

WHILE EXISTS (SELECT * FROM @trimmable WHERE trimval = CAST(SUBSTRING(@output, LEN(@output), 1) AS char(1)))
    SET @output = LEFT(@output, LEN(@output) - 1)

RETURN @output
END

GO

SELECT dbo.saneDecimal(1.00)

Answer 3

You could strip the trailing zeroes in a while loop:

declare @number decimal(5,2)
declare @str varchar(100)
set @number = 123.00
set @str = @number
while substring(@str,len(@str),1) in ('0','.',',')
    set @str = substring(@str,1,len(@str)-1)

But as AdaTheDev commented, this is more easily done client-side.

Answer 4

Simple and elegant? Not so much...but that's T-SQL for you:

DECLARE @number decimal(5,2) = 123.00
DECLARE @formatted varchar(5) = CAST(@number as varchar)

SELECT 
    LEFT(
        @formatted,
        LEN(@formatted)
            - PATINDEX('%[^0.]%', REVERSE(@formatted)) 
            + 1
    )

Answer 5

This way is pretty simple:

DECLARE @Number DECIMAL(5,2)

SELECT @Number = 123.65

SELECT FormattedNumber = CAST(CAST(@Number AS DECIMAL(3,0)) AS VARCHAR(4))

Returns '124'.

The only thing to consider is whether you want to round up/down, or just strip the zeroes and decimal points without rounding; you'd cast the DECIMAL as an INT in the second case.

Answer 6

What about:

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

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