Remove trailing zeroes using sql

Question

Possible Duplicate:
Remove trailing zeros from decimal in SQL Server

I am trying to use the round function and not show any of the trailing zeroes but still getting some zeroes at the end. I am suppose to get 10.4 but getting something like this:

10.400000

Here is my sql:

select round(AVG(CAST(k.TotalNumberDays AS numeric(12,2))),2) TotalNumber

How can i remove the trailing zeros here? I need to show only 10.4.

score 13 · Accepted Answer · answered Dec 26 '12 at 20:47

13

You just have to cast it as a decimal(12,2).

select cast(round(AVG(CAST(k.TotalNumberDays AS numeric(12,2))),2) as decimal(12,2)) TotalNumber

answered Dec 26 '12 at 20:47

Louis Ricci

20,804
5
48
62

Depending on the nature of the data, AVG is not necessary. For formatting only purposes, something like `cast(round(cast(X as numeric(12,2)),2) as decimal(12,2))` works fine. Thanks for your solution! – openwonk Jun 22 '15 at 22:51
What about if I don't know how many decimal places will be used? I can have: 12.2340000000000000 or 12.2345678900000000 for my decimal(18,15) – Mariusz Jan 13 '16 at 11:14
1

@aristo - there's no 1 liner SQL solution for this, it would be better to take care of it in your presentation layer. Then you can do a regular expression replace on the decimal value as a string and regex like "[0]+$" would work. – Louis Ricci Jan 13 '16 at 14:45

Randy Minder · Answer 2 · 2012-12-26T20:47:32.607

9

SQL Server supports formats float data type without trailing zeros, so you can remove trailing zeroes by casting the to a float. For example:

Select Cast(10.40000 as float)

This returns 10.4

However, this is a presentation issue and really should be done in your presentation layer instead of wasting resources casting to different data types.

edited Dec 26 '12 at 20:47

answered Dec 26 '12 at 20:41

Randy Minder

47,200
49
204
358

You are right about the presentation layer - except when we are updating data using Concat() to combine values into a string. – Fletcher Johnson Mar 29 '22 at 20:36

score 1 · Answer 3 · answered Dec 26 '12 at 20:41

If I try this:

SELECT(ROUND(CAST(10.4 AS numeric(12,2)), 2)

I get:

10.40

If you use the numeric or the decimal types, you will get up to as many zeros as you have set in the precision part of the data type.

In your example, you have specified 2 digits of precision, so you will always have up to 2 trailing zeros. Perhaps you need a different datatype, such as float.

score 1 · Answer 4 · answered Dec 26 '12 at 20:43

1

Just move your CAST out of avg like here:

select CAST(round(AVG(10.3543435),2) as numeric(12,1)) 

TotalNumber
---------------------------------------
10.4

(1 row(s) affected)

answered Dec 26 '12 at 20:43

infideltfo

95
1
6

Remove trailing zeroes using sql

4 Answers4

Linked