How to round a number in SQL Server

Question

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

As always, really appreciate the input.

- S

Answer 1

Create a custom function ( MSDN link ) and minimize the 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 2

Of course, try the following:

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

Answer 3

Presumably, you know how many decimal places you round, so you can apply to a decimal data type with the correct amount of DP outside ROUND

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