How to round number in SQL Server

Question

Hi All,

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

Answer 1

Sure, try this:

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

Answer 2

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)

Answer 3

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