SQL Server T-SQL User-Defined Function of the Week

Checking for Numeric Equality

Volume 1 Number 34    July 8, 2003

Visit the T-SQL UDF of the Week at:
http://www.NovickSoftware.com/UdfOfWeek/UDFofWeek.htm

Check out the UDF Frequently Asked Questions at:
http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm

To ask a UDF related question or to contribute a UDF,
send e-mail to udf@NovickSoftware.com

Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm

Although the SQL Server built-in function library has many of 
the most commonly used trigonometric functions.  Any that
were left out can easily be defined based on the built-in trig
functions or their mathematical formula.  We'll get to 
constructing one of the trig functions that SQL Server doesn't 
implement next week.  Part of the process of creating trig 
functions is testing them.  To do that you've got to compare
the result they return to an answer that you already know. That
turns out to be a little harder that it sounds at first.

The trigonometric functions take as their parameters a floating
point number (float) and always return a double precision
floating point result of data type float. There should be 
nothing surprising about that.

Working with floating point data in SQL Server has both 
advantages and disadvantages. For starters, there's the speed 
that comes from direct hardware support for floating point 
arithmetic in modern microprocessors.  

One of the other important advantage is the wide range of values
that a float can hold.  In the case of float the range is
from the largest value 1.79E+308 through -2.2E-308, which is the
smallest value.  You can choose any number of digits of precision
for the mantissa from 1 to 53, with a default of 53.  However,
SQL-Server only really implements 24-bit and 53 bit mantissas
represented by real and float respectively.

One of the problems that come up is comparing floating point
numbers for equality.  While floating point numbers are 
approximations to the value they represent, SQL Server 
performs equality comparisons exactly.  Even the slightest
difference between two numbers, for example 1.233434557890123
and 1.233434557890124 is regarded as a difference, even if the
difference is in the 15th place to the right of the decimal.

Try it:

SELECT CASE WHEN 1.233434557890123 = 1.233434557890124 
            THEN 'equal' 
            ELSE 'not equal' 
            END
GO
(Results)          
--------- 
not equal

To handle the complexity of comparing floating point numbers
I've devised a UDF, udf_Num_FpEqual, that compares two floats
to any requested number of digits of precision.  Here's the 
CREATE FUNCTION script:

/------- Start copying below this line -------------------------\
SET QUOTED_IDENTIFIER ON 
SET ANSI_NULLS ON 
GO

CREATE FUNCTION dbo.udf_NUM_FpEqual (

    @fArg1 float -- 1st number
  , @fArg2 float -- 2nd number
  , @nDigits2RtOfDecimal int = 0 -- Precision to Rt of decimal
     -- negative for left of decimal, like length parm of ROUND
)  RETURNS BIT -- 1 when equal else 0
   WITH SCHEMABINDING
/*
* Checks for equality of two floating point numbers with in a 
* specific number of digits to the right of the decimal place. 
* Returns 1 if equal, otherwise 0.
*
* Related Functions: same as udf_Unit_EqualFpBIT
*
* Example:
select dbo.udf_NUM_FpEqual (1.23456, 1234555, 3) as [Equal]
* Test:
PRINT 'Test 1 3 digits ' + case when 1=
          dbo.udf_NUM_FpEqual (1.23456, 1.234555, 3)
             THEN 'WORKED' ELSE 'ERROR' END
PRINT 'Test 2 6 digits '  + case when 0=
           dbo.udf_NUM_FpEqual (1.23456, 1.234555, 6)
           THEN 'WORKED' ELSE 'ERROR' END
*
* © Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically.
* Published in the T-SQL UDF of the Week Newsletter Vol 1 #34 
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS BEGIN

RETURN CASE WHEN ABS(@fArg1 - @fArg2) 
                < 0.499999999999999 
                  * POWER (CAST(10 as float)
                         , -1 *  @nDigits2RtOfDecimal) 
            THEN 1 ELSE 0 END

END
GO

GRANT EXEC, REFERENCES ON dbo.udf_Num_FpEqual to PUBLIC
GO
\-------Stop copying above this line ---------------------------/

Asking if numbers are equal is a matter of supplying them
and providing the number of digits to the right of the decimal
that you want to allow.  Here's an example:
/------- Start copying below this line -------------------------\
DECLARE @Arg1 float, @Arg2 float
SELECT @Arg1 = 1.233434557890123, @Arg2 = 1.233434557890124
SELECT dbo.udf_Num_FpEqual (@Arg1, @Arg2, 14) [14 Digits]
     , dbo.udf_Num_FpEqual (@Arg1, @Arg2, 15) [15 Digits]
     , dbo.udf_Num_FpEqual (@Arg1, @Arg2, 16) [16 Digits]
GO
\-------Stop copying above this line ---------------------------/
(Results)
14 Digits 15 Digits 16 Digits 
--------- --------- --------- 
        1         0         0 

udf_Num_FpEqual returns 1 when the numbers are considered equal
and 0 otherwise.

I decided that to be considered equal, the difference needed
to be less than half the size of the last place to the right
of the decimal.  I'm not sure that a mathematician would do the 
same.  Other candidates instead of .49999999999 are .1, 
.999999999999999999, or even 1.  I'd like to hear from any
mathematicians with suggestions about what's best here.

Do you have a great UDF that you'd like to share?  Or maybe you
have a T-SQL problem that you think could be solved by a UDF
but you don't know how? Send them to:

UDF@NovickSoftware.com 

and they might be published in this newsletter.  I try and 
respond to every request that I get.  

Thanks,  
Andrew Novick