A fix() function in T-SQL - restricting the significant digits in a number
Someone today asked me for a SQL Server function
that would round a number to the first two significant places. The idea
would be that 0.243 would round to 0.24, 0.00592 would round to 0.0059,
and 34600 would round to 35000.
Like all good developers, I start with creating some tests. :)
I'm
not using a proper testing tool here, this is just for fun - so I'm
going to just make a resultset with two columns - the one that I should
get, and the one that I expect to get. And I'll throw in a couple of
extra numbers, to make sure I cater for the negative cases as well.
select * from
( select 0.243 param, dbo.fix(0.243) result, 0.24 wanted
union all select 0.00592, dbo.fix(0.00592), 0.0059
union all select 34600, dbo.fix(34600), 35000
union all select -3323, dbo.fix(-3323), -3300
union all select -3.59, dbo.fix(-3.59), -3.6
) t
where t.result <> t.wanted
Obviously
this won't work yet, I don't have a function called fix (which I'm
naming for the comparison with the 'fix' function in other systems). So
let's create it:
create function dbo.fix(@num float) returns float as
begin
return (@num)
end
I'm using float because I'm lazy. I could use numeric, but float is quick to type, and does the job for now.
Right. Now I run my test, and all five results come back. Lovely. So now let's fix the function.
So
how do we do this... well, the round() function in SQL Server will
round a number nicely. It takes a parameter which is the number of
decimal places you want. And it takes negative numbers. round(12345,-3)
gives 12000. That's just what I'm after. So let's think about it. I
want the length of the number. The length of 12345 is obviously 5 - so
that works. I can convert the number into a string, count the length
and get 5. Then I can get -3 from it by subtracting it from 2, which is
the number of significant places I want.
Terrific. That'll work for positive integers. But the length of "1.2345" is 6, which clearly isn't right.
Here's
where I feel myself turning into a real geek. I'm going to use the
log10() function. log10(12345) is 4.09. Better still, log10(999) is
2.9996 and log10(1000) is 3. That's terrific. I can round that number
down, add one, and there's my number length. It even works for
fractions. log10(1.2345) is a little over 0, log10(0.9) is just under
0. log10(0.00343) is about -2.5. So if I round all these DOWN (that's
the floor() function), and add one, I get what I need the length to be.
I can't use the ceiling() function, because that wouldn't work for 1000
- for which I want a length of 4, not 3. I do actually need to round
down and then add one, in case rounding down doesn't do anything.
So now I have my 'length', and if I subtract that from 2, then hopefully I get the number that I can use for the round function.
alter function dbo.fix(@num float) returns float as
begin
declare @res float
select @res = round(@num,2-(1+floor(log10(@num))))
return (@res)
end
Well
this seems to do it. Of course I could change the 2 to a 1, and stop
adding one to my floor. I run my test query, and no rows are returned.
Great. All done.
Except that it isn't. High-school maths reminds
me that you can't take the log of a negative number or zero. So what's going on
with my tests? I check the Messages (rather than the results grid), and
I see a domain error has occurred. It'd be nice if this would kick a
proper error, one that would display red text and stop my query from
running at all, but such is life.
Either way, we know it's a problem now, so we can fix it.
I
can't take the log of a negative number, so let's check to see if the
number if negative, and if it is, we can use the log of the negative
number instead. I mean, I still want to round -3.6 with a value of 1,
and -34334 with -3. This translates into the abs() function. But I need to cater for zero, so I'll put a case statement into my function:
alter function dbo.fix(@num float) returns float as
begin
declare @res float
select @res = case when @num = 0 then 0 else round(@num,1-floor(log10(abs(@num)))) end
return (@res)
end
Now my test works without an error message. "(0 row(s) affected)" is what I want to see, and there it is. I'm happy.
A minor change is to let it take another function to show many significant digits are required. Simple change:
alter function dbo.fix(@num float, @digits int) returns float as
begin
declare @res float
select @res = case when @num = 0 then 0 else round(@num,@digits-1-floor(log10(abs(@num)))) end
return (@res)
end
And the guy who asked me for this? Well, he's gobsmacked that there really is a use for logarithms in the real world.
A postscript: The friend of mine died in July 2006, aged 48. He will long be remembered.