CODEWITHSUNDEEP
sql-serversql-server-2005t-sqlsql-server-2008
scarpacci
scarpacci

Reputation: 9194

How to round number in SQL Server

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

Upvotes: 3

Views: 2818

Answers (3)

JamWheel
JamWheel

Reputation: 300

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

Upvotes: 0

NakedBrunch
NakedBrunch

Reputation: 49413

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)

Upvotes: 1

Denis Valeev
Denis Valeev

Reputation: 6015

Sure, try this:

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

Upvotes: 1

Related Questions