SQL Mathematical Functions

Next Post
By Admin at 3 Jun 2010, 15:47 PM
  • SQL mathematical functions are used to perform a mathematical operation and to return the result set of the operation. All math functions are deterministic except the RAND function.

    Function NameDescriptionExample
    ABS

    This function returns the positive value of a numeric parameter. It provides the positive value in the output. When the absolute value of a number is greater than the largest number that can be represented using the specific data type, an overflow error results.

    Syntax:

    ABS(numeric_expression)

    1. SELECTABS(-9.0)

    Output:

    9.0

    1. DECLARE @i int;
    2. SET @i = -2147483648;
    3. SELECTABS(@i);
    4. GO

    Output:

    Msg 8115, Level 16, State 2, Line 5 Arithmetic overflow error converting expression to data type int.

    ACOS

    This trigonometric function accepts a cosine value as input and returns the angle, expressed in radians.

    Syntax:

    ACOS(float_expression)

    1. SELECT ACOS(-.40)

    Output:

    1.98231317286238

    ASIN

    This trigonometric function accepts a sine value as input and returns the angle, expressed in radians.

    Syntax:

    ASIN(float_expression)

    1. SELECT ASIN(-4.0)

    Output:

    -0.411516846067488

    ATAN

    This trigonometric function accepts a tangent value as input and returns the angle, expressed in radians.

    Syntax:

    ATAN (float_expression)

    1. SELECT'The ATAN of -49.01 is: ' +
    2. CONVERT(varchar, ATAN(-49.01))

    Output:

    The ATAN of -49.01 is: -1.5504

    ATN2

    This function accepts two float parameters and uses them as a co-ordinate point (x, y). It returns the angle, expressed in radians, between the positive X-axis and the ray formed by the origin (0, 0) and the input co-ordinate point (x, y).

    Syntax:

    ATN2 (float_expression , float_expression)

    1. SELECT ATN2(-.40,-1.0)

    Output:

    -2.76108627647743

    CEILING

    This function accepts a numeric parameter. It returns the smallest integer value greater than or equal to that input parameter.

    Syntax:

    CEILING

    1. SELECT CEILING($250.890)

    Output:

    251.00

    1. SELECT CEILING($-250.890)

    Output:

    -250.00

    1. SELECT CEILING($0.0)

    Output:

    0.00

    COS

    This trigonometric function accepts an angle, expressed in radians, and returns its cosine value.

    Syntax:

    COS(float_expression)

    1. SELECT COS(-.50)

    Output:

    0.877582561890373

    COT

    This trigonometric function accepts an angle, expressed in radians, and returns its cotangent value.

    Syntax:

    COT(float_expression)

    1. SELECT COT(-.50)

    Output:

    -1.83048772171245

    DEGREES

    This function returns the corresponding angle in degrees for an angle specified in radians.

    Syntax:

    DEGREES(numeric_expression)

    1. SELECT DEGREES(1.0471975511966)

    Output:

    60.000000000000135000

    EXP

    This function returns the exponential value of the specified float parameter.

    Syntax:

    EXP(float_expression)

    1. SELECT EXP(5.4)

    Output:

    221.406416204187

    FLOOR

    This function accepts a numeric value as a single parameter. It returns the largest integer value which is smaller than or equal to that input parameter.

    Syntax:

    FLOOR(numeric_expression)

    1. SELECT FLOOR(123.85)

    Output:

    123

    1. SELECT FLOOR(-123.85)

    Output:

    124

    LOG

    This function returns the logarithm value of a float input parameter.

    Syntax:

    LOG(float_expression)

    1. SELECT LOG(5.8)

    Output:

    1.75785791755237

    LOG10

    This function returns the Base-10 logarithm value of a float input parameter.

    Syntax:

    LOG10(float_expression)

    1. SELECT LOG10(5.8)

    Output:

    0.763427993562937

    PI

    This function returns the value of the constant PI. It accepts no parameters.

    Syntax:

    PI()

    1. SELECT PI()

    Output:

    3.14159265358979

    POWER

    This function returns the value of the first input parameter when it is raised to the power of the second parameter.The float input parameter, listed first, is the base. The second input parameter is the numeric exponent.

    Syntax:

    POWER(float_expression,y)

    1. SELECT POWER(2,5)

    Output:

    32

    RADIANS

    This function returns the corresponding angle, expressed in radians, when an angle measure is provided in degrees as the input parameter.

    Syntax:

    RADIANS(numeric_expression)

    1. SELECT RADIANS(0.354)

    Output:

    0.006178465552059930

    RAND

    This function generates a random number, between 0 and 1, and returns the result as a float type. It accepts an optional parameter of integer type to use as a seed for the random number generation.

    Syntax:

    RAND([seed])

    1. SELECT RAND(),RAND(200)
    ROUND

    This function rounds the value of the first input parameter to the precision specified by the second input parameter. It optionally takes in a third input parameter. If the value of the third parameter is any value other than 0, then the input value will be truncated.

    Syntax:

    ROUND(numeric_expression, length, [truncate flag])

    1. SELECT ROUND(9104.4545, 3)

    Output:

    9104.4550

    1. SELECT ROUND(9104.4545, 3, 2)

    Output:

    9104.4540

    1. SELECT ROUND(9104.4545, 2)

    Output:

    9104.4500

    SIGN

    This function returns the sign of the input parameter value. It returns -1 for negative values, 0 for zero values, and 1 for positive values.

    Syntax:

    SIGN(numeric_expression)

    1. SELECT SIGN(90.354)

    Output:

    1.000

    1. SELECT SIGN(-90.354)

    Output:

    -1.000

    SIN

    This trigonometric function accepts an angle, expressed in radians, and returns its sine value.

    Syntax:

    SIN(float_expression)

    1. SELECT SIN(.60)

    Output:

    0.564642473395035

    SQRT

    This function returns the square root of a float parameter.

    Syntax:

    SQRT(float_expression)

    1. SELECT SQRT(1024)

    Output:

    32

    SQUARE

    This function returns the square value of a float parameter.

    Syntax:

    SQUARE(float_expression)

    1. SELECT SQUARE(25.52)

    Output:

    651.2704

    TAN

    This trigonometric function accepts an angle, expressed in radians, and returns its tangent value.

    Syntax:

    TAN(float_expression)

    1. SELECT TAN(.60)

    Output:

    0.684136808341692


    Comments

     

    Post a comment

    Please correct the following: