A Fix() Function in T-SQL

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. 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. 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 round(@num,1-floor(log10(@num)))
else round(@num,1-floor(log10(-@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 round(@num,@digits-1-floor(log10(@num)))
else round(@num,@digits-1-floor(log10(-@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.

Original Source: http://www.sqlservercentral.com/articles/Advanced+Querying/afixfunctionintsql/2487/