How to round number in SQL Server

Question

I have a scenario where I need to round and then remove the extra zeros from numbers. So if I have a number that I have rounded (12.456400000) I want the zeros to be removed. Is there a function I can use to remove those numbers? The round function appears to leave the zeros in place?

As always greatly appreciate the input.

--S

duplicate question: http://stackoverflow.com/questions/2938296/remove-trailing-zeros-from-decimal-in-sql-server — JeffO, Sep 22 '10 at 16:42

score 1 · Answer 1 · answered Sep 22 '10 at 16:36

1

Sure, try this:

select replace(rtrim(replace('12.456400000', '0', ' ')), ' ', '0')

answered Sep 22 '10 at 16:36

Denis Valeev

5,975
35
41

NakedBrunch · Accepted Answer · 2010-09-22T17:04:39.440

1

Create a User Defined Function (MSDN link) and minimize code.:

CREATE FUNCTION [dbo].[RemoveTrailingZeros] 
(
    @Value decimal
)
RETURNS decimal
AS
BEGIN
    RETURN replace(rtrim(replace(@Value, '0', ' ')), ' ', '0')
END

You would use it as follows:

SELECT dbo.RemoveTrailingZeros(12.456400000)

edited Sep 22 '10 at 17:04

answered Sep 22 '10 at 16:47

NakedBrunch

48,713
13
73
98

Too bad it would leave whole numbers with a trailing period. `12.00000 -> 12.` – Nick Rolando Apr 28 '11 at 19:23
@Shredder this function in meant to remove all trailing zeros. If you want to specify the number of digits you can use JamWheel's CAST solution – Rafael Piccolo Sep 25 '12 at 17:54

score 0 · Answer 3 · answered Sep 28 '10 at 14:05

0

Presumably you know how many decimal places you are rounding to, so you can cast to a decimal data type with the correct number of DP outside the ROUND

CAST(ROUND(Field, 4) AS DECIMAL(12, 4))

answered Sep 28 '10 at 14:05

JamWheel

300
2
7

How to round number in SQL Server

3 Answers3