Round to n Significant Figures in SQL

sql sql-server tsql rounding significant-digits
Paul picture Paul · Dec 17, 2009 · Viewed 20k times · Source

I would like to be able to round a number to n significant figures in SQL. So:

123.456 rounded to 2sf would give 120
0.00123 rounded to 2sf would give 0.0012

I am aware of the ROUND() function, which rounds to n decimal places rather than significant figures.

Answer

Brann picture Brann · Dec 17, 2009

select round(@number,@sf-1- floor(log10(abs(@number)))) should do the trick !

Successfully tested on your two examples.

Edit : Calling this function on @number=0 won't work. You should add a test for this before using this code.

create function sfround(@number float, @sf int) returns float as
begin
    declare @r float
    select @r = case when @number = 0 then 0 else round(@number ,@sf -1-floor(log10(abs(@number )))) end
    return (@r)
end

Related questions

Truncate (not round) decimal places in SQL Server

I'm trying to determine the best way to truncate or drop extra decimal places in SQL without rounding. For example: declare @value decimal(18,2) set @value = 123.456 This will automatically round @value to be 123.46, which is good in most cases. However, for …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More