Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

A Fix() Function in T-SQL Expand / Collapse
Author
Message
Posted Tuesday, June 27, 2006 1:45 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, August 2, 2014 1:58 AM
Points: 164, Visits: 368
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rfarley/afixfunctionintsql.asp

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://sqlblog.com/blogs/rob_farley
Post #290551
Posted Tuesday, July 25, 2006 2:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 26, 2014 2:45 PM
Points: 2,907, Visits: 1,829
Solution for constipated mathematicians, work it out with paper and pencil, if that doesn't work use LOGs

LinkedIn Profile
Newbie on www.simple-talk.com
Post #296989
Posted Tuesday, July 25, 2006 3:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:09 AM
Points: 6,020, Visits: 8,288

Nice function.

Tip 1: Instead of the CASE to get rid of negative values, use ABS.

Tip 2: Add a CASE to work around the domain error you'd get from LOG10(0).

Tip 3: Stop being lazy and use decimal instead of float. Because float can't represent many values exactly, you run the risk of errors. For instance:

SELECT dbo.fix(1.15)   -- Returns 1.1 instead of 1.2

alter function dbo.fix(@num numeric(36,18), @digits int) returns numeric(36,18) 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




Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #296999
Posted Tuesday, July 25, 2006 6:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 7, 2013 11:44 AM
Points: 51, Visits: 40
This article is quite a coincidence, I was just thinking this morning, how could I use log? Now if I could just remember how they worked, it's been 14 years since I've last used them.


Post #297042
Posted Tuesday, July 25, 2006 7:28 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
Having completed IT + Maths degrees at university it is nice to see someone using some maths in the real world - I reckon 99/100 SQL developers would've used a varchar conversion with something to cater for the possible decimal point and minus signs and lived with the performance loss.  Nice article and good thinking!


Post #297061
Posted Tuesday, July 25, 2006 8:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:22 PM
Points: 2,121, Visits: 3,207

Very sweet!

But why the extra overhead of declaring and setting a variable?  Why not just return the calc'd value?

...
BEGIN
RETURN CASE WHEN ...
END



SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #297090
Posted Thursday, July 27, 2006 10:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 5:12 PM
Points: 3, Visits: 12

Nice to see someone using LOGs. But I would trust your results more if you displayed them, instead of using the WHERE expected_result <> actual_result.

The problem I found was that you don't get exact representation when using float. Frankly, I have never used float before, so maybe I am doing something incorrectly, but note the example below.

declare   @num float
           , @res float
           , @digits int
SET @num = 1.23456678
SET @digits = 4
select @res = case when @num > 0
                      then round(@num,@digits-1-floor(log10(@num)))
                      else round(@num,@digits-1-floor(log10(-@num)))
                    end
select @num as ORIGINAL_NUMBER, @res AS RESULT 
       , @digits as NBR_SIG_DIGITS

RESULT IS:
ORIGINAL_NUMBER         RESULT                  NBR_SIG_DIGITS
1.23456678             1.2349999999999999            4

Probably not what you really want (I assume you want 1.235 ??)



 




Post #297859
Posted Thursday, July 27, 2006 12:07 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:09 AM
Points: 6,020, Visits: 8,288

Hi Skip,

>>The problem I found was that you don't get exact representation when using float<<

Of course you don't - check Books Online. Float is an approximate-number data type. For most numbers, it can only store a (close) approximization.

Internally, float uses base-2 representation. As a result, only numbers that can be written as x + (y / EXP(2, z)) where x and y are integers and z is a non-negative integer can be represented exactly in that representation. Just like our common base-10 representation can only represent an approximation of 1/3, float can only represent an approximation of 1.235.

That's one of the reasons why I wrote that the function should use decimal instead of float in my previous reply (though I now see that I forgot to change the datatype for the internal variable).




Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #297885
Posted Monday, July 31, 2006 12:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, August 2, 2014 1:58 AM
Points: 164, Visits: 368
True, I should've used numeric. I said in the article it was out of laziness.

I chose float originally because that's the type that the trigonometry functions use. Ideally I'd have it return the same type as is passed in (the way that the others work). But I knew numeric would be better... I was just being lazy.

As for using abs... yes - that would've been better. And I can't believe I didn't see that it doesn't work for 0. That's awful of me.

Thanks Hugo...

Rob


Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://sqlblog.com/blogs/rob_farley
Post #298292
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse