CODEWITHSUNDEEP
sqlt-sql
Ian Henry
Ian Henry

Reputation: 22413

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.

Upvotes: 10

Views: 27244

Answers (10)

Christopher Rose
Christopher Rose

Reputation: 1

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.

Upvotes: 0

Mark Micallef
Mark Micallef

Reputation: 2797

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().

Upvotes: 3

Andrew
Andrew

Reputation: 381

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

Upvotes: 0

user1664143
user1664143

Reputation: 37

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.

Upvotes: -2

Frank Kalis
Frank Kalis

Reputation: 1352

What about: 

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

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

Upvotes: 14

user250118
user250118

Reputation: 41

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.

Upvotes: 4

Mark Brackett
Mark Brackett

Reputation: 85685

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
    )

Upvotes: 0

Andomar
Andomar

Reputation: 238296

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.

Upvotes: 0

roufamatic
roufamatic

Reputation: 18495

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)

Upvotes: 0

Randy Minder
Randy Minder

Reputation: 48522

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

Upvotes: 0

Related Questions