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

Volume 1 Number 35    July 15, 2003

Testing the Hyperbolic Tangent Function

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

Last week I wrote about the issue of comparing floating point 
numbers do a specific degree of precision.  The function created
for that issue, udf_Num_FpEqual did the job by taking the 
difference between the two numbers and then comparing it to
a sufficiently small number.  

That function is necessary for completing this week's issue, 
which is devoted to the function udf_Trig_TANH.  udf_Num_FpEqual
is needed to test the trigonometric functions. 

SQL Server has several built-in trigonometric functions but it
doesn't have them all. I know of 14, mostly hyperbolic functions,
that it doesn't include.  The hyperbolic tangent is just one
of them.

I've used a definition of TANH based on the exponential function
that I found on Wolfram's web site.  The link is in the header
of the function definition.

Here's the CREATE FUNCTION script:
/------- Start copying below this line -------------------------\
SET QUOTED_IDENTIFIER ON 
SET ANSI_NULLS ON 
GO

CREATE    FUNCTION dbo.udf_Trig_TANH (

    @x float -- number to find hyperbolic tangent for
)   RETURNS float -- Hyperbolic tangent of @x
    WITH SCHEMABINDING
/*
* Returns the hyperbolic tangent of a number. It's equal to
* sinh(x)/cosh(x) or the expression found below.
* See http://mathworld.wolfram.com/HyperbolicTangent.html
*
* Example:
select dbo.udf_Trig_TANH (0)   
*
* Test: 
PRINT 'Test 1  1   ' + case when 1=
       dbo.udf_NUM_FpEqual(0.761594156, dbo.udf_Trig_TANH(1), 9)
       THEN 'WORKED' ELSE 'ERROR' END
PRINT 'Test 2  0   ' + case when 1=
       dbo.udf_NUM_FpEqual(0.0, dbo.udf_Trig_TANH(0), 9)
       THEN 'WORKED' ELSE 'ERROR' END
PRINT 'Test 3  0.5   ' + case when 1=
       dbo.udf_NUM_FpEqual(0.462117157, dbo.udf_Trig_TANH(0.5), 9)
       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 as T-SQL UDF of Week Newsletter Vol 1 #35 
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS BEGIN

RETURN (( POWER(EXP(1), @x) -  POWER(EXP(1),-@x) ))
      /(( POWER(EXP(1), @x) +  POWER(EXP(1),-@x) ))

END
GO

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

The tests from the UDF header demonstrate how udf_Num_FpEqual 
fits into the picture.  Let's execute them:

PRINT 'Test 1  1   ' + case when 1=
       dbo.udf_NUM_FpEqual(0.761594156, dbo.udf_Trig_TANH(1), 9)
       THEN 'WORKED' ELSE 'ERROR' END
PRINT 'Test 2  0   ' + case when 1=
       dbo.udf_NUM_FpEqual(0.0, dbo.udf_Trig_TANH(0), 9)
       THEN 'WORKED' ELSE 'ERROR' END
PRINT 'Test 3  0.5   ' + case when 1=
       dbo.udf_NUM_FpEqual(0.462117157, dbo.udf_Trig_TANH(0.5), 9)
       THEN 'WORKED' ELSE 'ERROR' END
GO        
(Results)
Test 1  1   WORKED
Test 2  0   WORKED
Test 3  0.5   WORKED

I want the tests to check the result, not just print out the
result of the UDF.  After all, I really don't remember the 
TANH of anything.

Each of the tests have to include the correct answer.  Where did
I get them?  Well, I've lost my 9th grade trig book with all the
tables.  Thank goodness I don't have to go back to that any more.
Instead I used Excel as a reference and got the values from it.

You can download the spreadsheet that I used to get the test values
by clicking here. 

Please share this newsletter with anyone interested in
SQL Server.

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
+--------------------------------------------------------------+