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.

What about:

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

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

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.

For controlled formatting of numbers in T-SQL you should use the FORMAT() function. For example:

DECLARE @number DECIMAL(9,2); SET @number = 1234567.12;
DECLARE @formatted VARCHAR(MAX); SET @formatted = FORMAT(@number, 'N0', 'en-AU');
PRINT @formatted;

The result will be:

1,234,567

The arguments to the FORMAT() function are:

FORMAT(value, format [, culture])

The value argument is your number. The format argument is a CLR type formatting string (in this example, I specified "normal number, zero precision"). The optional culture argument allows you to override the server culture setting to format the number as per a desired culture.

See also the MSDN ref page for FORMAT().

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

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)

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.

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
    )

Use the Format(value,format string,culture) function in SQL Server 2012+

If you have SQL Server 2012 or Greater you can use the format function like this:

select format(@number,'0') as FormattedNumber

Of course the format function will return an nvarchar, and not a varchar. You can cast to get a specific type.

Also, take a look at the T-SQL STR function in Books Online; this can be used for formatting floats and might work for your case. For some reason it doesn't come up in Google searches relating to this problem.