Paul - 4 months ago 39

SQL Question

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

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

Source (Stackoverflow)

Comments