Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

A Fix() Function in T-SQL

By Rob Farley,

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.

Addendum:
 
Hugo Kornelis points out to me that my use of float isn't just lazy, it produces a wrong result for certain values (such as dbo.fix(1.15, 2)). And logs don't work for zero. And also, I could've used ABS instead of the case statement. That being the case, this is a better fix() function:
 
alter function dbo.fix(@num numeric(36,18), @digits int) returns numeric(36,18) as
begin
return (case when @num = 0 then 0 else round(@num,@digits-1-floor(log10(abs(@num)))) end)
end

Also, the gobsmacked friend of mine died last week. You can read the tribute comments about him at kelvinwilson.blogspot.com. He will be missed.

Total article views: 14339 | Views in the last 30 days: 10
 
Related Articles
BLOG

Round() function in SQL Server

Round():-This function is used to round a numeric field to the number of decimals specified. Synt...

FORUM

Round function in MDX

Round function in MDX

FORUM

Round function in MDX

Round function in MDX

FORUM

sql rounding numbers

I don't want my numbers rounded

ARTICLE

Custom Rounding and Truncation of Numbers in MDX

MDX applies "bankers' rounding" algorithm in its ROUND() function. This article shows you how to avo...

Tags
advanced querying    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones