Mathematical Functions In SQL

Article

Functions are a type of object in SQL that contain a set of SQL queries. Each function takes inputs in the form of parameters and perform a set of operations and returns output. A function returns a single result set.

SQL Server contains many types of functions, like Scalar-valued, Table-valued and system functions.

System functions consist of a number of functions as shown in the following image.

Figure 1: Function

Each category of system function contains a number of functions so we can't read all the functions in a single article. Today we learn about Mathematical System Functions.

Mathematical System Functions

A mathematical function performs a mathematical operation on numeric expressions and returns the result of the operation. SQL Server provides a variety of mathematical function. These math functions are specific to applications using Trigonometry, Calculus and Geometry. Arithmetic functions, such as ABS, CEILING, DEGREES, FLOOR, POWER, RADIANS and SIGN, return a value having the same data type as the input value. Trigonometric and other functions, including EXP, LOG, LOG10, SQUARE and SQRT, cast their input values to float and return a float value.

SQL Server contains the following Mathematical Functions:

Figure 2: Mathematical Function

For a better understanding, let's see all the preceding functions with easy examples.

Abs(): The Abs() function returns the absolute value. That means a positive value of the specified numeric expression. If we provide a positive number then no change will occur and output will be remain the same. If we provide a negative number then the Abs() function converts the negative number to a positive number. The value being processed can be any numeric data type and the function returns a value of the same type. The Abs() function doesn't work for the bit data type.

Figure 3: Abs

Syntax

ABS ( numeric_expression )

Example

SELECT ABS(-25) AS A1, ABS(25) AS A2 ,ABS(-25.50) A3 ,ABS(25.25) AS A4

Output

Figure 4: Abs Output

Example

SELECT ABS(-2147483648) A1 ,ABS(2147483648) A2

Output

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

This error message indicates that if we provide the value of any numeric expression outside its range then the ABS() function will not work and the SQL Server generates the error.

ACOS(): A mathematical function that returns the angle, in radians, whose cosine is the specified float expression, also called arccosine.

Syntax

ACOS ( float_expression )

The return type of the function is float.

Figure 5: Return type of function

Float_expression

Is an expression of the type float or of a type that can be implicitly converted to float, with a value from -1 through 1. Values outside this range return NULL and report a domain error.

Example

SELECT ACOS(-0.5) A1 ,ACOS(0.5) A2 , ACOS(1) A3

Figure 6: Output2

Example

SELECT ACOS(1.2) A1 ,ACOS(0.5) A2 , ACOS(1) A3

Output

Msg 3623, Level 16, State 1, Line 1
An invalid floating point operation occurred.

In the preceding example we provide 1.2 as the input to the ACOS() function and that is outside the range of the ACOS() function so it generates the error message.

ASIN(): ASIN() function returns the angle, in radians, whose sine is the specified float expression. This is also called arcsine. The return type is Float.

Syntax

ASIN ( float_expression )

float_expression

Is an expression of the type float or of a type that can be implicitly converted to float, with a value from -1 through 1. Values outside this range return NULL and report a domain error.

Figure 7: ASIN

Example

SELECT ASIN(0.5) A1 , ASIN(0.8) A2 ,ASIN(1) A3

Output

Figure 8: Output3

Example

/* The value @angle is outside the range
so ASIN function throw an error */
DECLARE @angle float
SET @angle = -1.01
SELECT 'The ASIN angle is: ' + CONVERT(varchar, ASIN(@angle))
GO

Output

Msg 3623, Level 16, State 1, Line 5
An invalid floating point operation occurred.

ATAN(): The ATAN() function returns the angle in radians whose tangent is a specified float expression. This is also called arctangent. The return type of the function is float.

Syntax

ATAN ( float_expression )

float_expression: Is an expression of the type float or of a type that can be implicitly converted to float.

Figure 9: ATAN

Example

SELECT ATAN(45) A1, ATAN(55) A2, ATAN(-25) A3

Output

Figure 10: ATAN Output

ATN2(): the ATN2() function returns the angle, in radians, between the positive x-axis and the ray from the origin to the point (y, x), where x and y are the values of the two specified float expressions. The return type of the function is float.

Syntax

ATN2 ( float_expression , float_expression )

Figure 11: ATN2

Float_expression: Is an expression of the float data type.

Example

SELECT ATN2(45,45) A1 , ATN2(35,148) A2 ,ATN2(88,345) A3

Output:

Figure 12: ATN2 Output

CEILING(): The CEILING() function returns the smallest integer greater than, or equal to, the specified numeric expression. The return type of the function is float.

Syntax

CEILING ( numeric_expression )

numeric_expression

Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Figure 13: CEILING

Example

SELECT CEILING(124.45) A1, CEILING(-124.15) A2, CEILING(124) A3

Output

Figure 14: Ceiling Output

COS(): The COS() function returns the trigonometric cosine of the specified angle, in radians, in the specified expression. In other words, it returns the cosine of an angle that is provided in radians. The return type of the function is float.

Syntax

COS ( float_expression )

Figure 15: Cos

Float_expression

Is an expression of type float.

Example

SELECT COS(1.046) A1 , COS(0) A2 , COS(0.785) A3

Output

Figure 16: COS Output

Now we take the same example for an angle of 60, 0 and 45 degrees.

SELECT COS(60*3.14/180) A1 , COS(0) A2 , COS(45*3.14/180) A3

Figure 17: Cos Output 2

COT(): The COT() function returns the trigonometric cotangent of the specified angle, in radians, in the specified float expression. In other words it returns the cotangent of an angle in radians. The return type of the function is float.

Syntax

COT ( float_expression )

Figure 18: Cot

Float_expression

Is an expression of type float or of a type that can be implicitly converted to float.

Example

SELECT COT(124) A1 ,COT(-45) A2 ,COT(78.45) A3

Output

Figure 19: Cot Output

DEGREES(): The DEGREES() function returns the corresponding angle in degrees for an angle specified in radians. The return type of DEGREES() is the same type as numeric_expression.

Syntax

DEGREES ( numeric_expression )

Figure 20: Degrees

numeric_expression

Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Example

SELECT DEGREES(PI()) A1, DEGREES(PI()/2) A2,DEGREES(PI()/4) A3

Output

Figure 21: Degrees output

EXP(): The EXP() function returns the exponential value of the specified float expression. The return type of the function is float. The EXP() function uses the constant e (2.718281…) as the base.

Syntax

EXP ( float_expression )

Figure 22: EXP

Example

SELECT EXP(2) A1,EXP(3) A2,EXP(10) A3

Output

Figure 23: EXP Output

Here

Exp(2) represent e^2 , Exp(3) represent e^3 , Exp(10) represent e^10
Value of e=22/7 =2.71 approximate

FLOOR(): The FLOOR() function returns the largest integer less than or equal to the specified numeric expression. The FLOOR() function returns the same type as numeric_expression.

Syntax

FLOOR ( numeric_expression )

Figure 24: Floor

numeric_expression

Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Example

SELECT FLOOR(-124.14) A1, FLOOR(124) A2 ,FLOOR(124.15) A3

Output

Figure 25: Floor Output

LOG(): The LOG() function returns the natural logarithm of the specified float expression in SQL Server. the return type of the function is float.

Syntax

LOG ( float_expression [, base ] )

By default the value of the base is “e”, in other words 22/7.

Figure 26: Log

float_expression : Is an expression of type float or of a type that can be implicitly converted to float.

base: Optional integer argument that sets the base for the logarithm.

Example

SELECT LOG(12) A1, LOG(25) A2, LOG(100) A3

Output

Figure 27: log Output

SELECT LOG(49,7) Base7, LOG(125,5) Base5, LOG(64,4) Baes4

Output

Figure 28: Log Output 2

LOG10( ): the LOG10 function returns the base-10 logarithm of the specified float expression. The return type of the function is float.

Syntax

LOG10 ( float_expression )

Figure 29: Log 10

float_expression

Is an expression of type float or of a type that can be implicitly converted to float.

Example

SELECT LOG10(1000) A1, LOG10(10000) A2, LOG10(10050) A3

Figure 30: log10 Output

PI(): The PI function returns the constant value of PI. The return type is a float that is a constant value.

Syntax

PI ( )

Figure 31: Pi

Example

SELECT PI() A1 ,PI()*2 A2 , PI()*3 A3

Figure 32: Pi Output

POWER(): The POWER function returns the value of the specified expression to the specified power. The return type is float.

Syntax

POWER ( float_expression , y )

Figure 33: Power

float_expression: Is an expression of type float or of a type that can be implicitly converted to float.

y: Is the power to which to raise float_expression. y can be an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Example

SELECT POWER(2,3) A1, POWER(5,5) A2, POWER(4,4) A3

Figure 34: Power Output

RADIANS(): The RADIANS function returns radians when a numeric expression, in degrees, is entered. The return type of the function is float.

Syntax

RADIANS ( numeric_expression )

Figure 35: Radians

numeric_expression

Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Example

SELECT RADIANS(180) Approximate, RADIANS(180.0) Exact , RADIANS(60) Approximate, RADIANS(60.0) Exact

Figure 36: Radian Output

RAND(): The RAND function returns a pseudo-random float value from 0 through 1, exclusive. The return type of the Rand function is float.

Syntax

RAND ( [ seed ] )

Figure 37: Rand

Figure 38: Rand2

seed: Is an integer expression (tinyint, smallint or int) that gives the seed value. If a seed is not specified, the SQL Server Database Engine assigns a seed value at random. For a specified seed value, the result returned is always the same.

Example

SELECT RAND() A1,RAND() A2, RAND() A3

Figure 39: Seed

SELECT RAND(2) A1,RAND(2) A2, RAND(2) A3

Figure 40: Seed2

ROUND(): The ROUND function returns a numeric value, rounded to the specified length or precision. The return type of the function is the same as the numeric expression.

Syntax

ROUND ( numeric_expression , length [ ,function ] )

Figure 41: Round

Figure 42: Round2

numeric_expression: Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

length: The number of decimal places rounded to. This value must be a positive or negative integer. If this parameter is omitted, the ROUND function will round the number to 0 decimal places. If it is positive then the decimal part of the number is rounded and if it is negative then the number is rounded on the left side of the decimal point.

function (Optional): The operation can be either 0 or any other numeric value. When it is 0 (or this parameter is omitted), the ROUND function will round the result to the number of decimal_places. If operation is any value other than 0, the ROUND function will truncate the result to the number of decimal_places.

Example

SELECT ROUND(225.715, 2) Round_ UNION all /* result is rounded because 3rd parameter is omitted */
SELECT ROUND(225.715, 2, 0) Round_ UNION ALL /* result is rounded because 3rd parameter is 0 */
SELECT ROUND(225.715, 2, 1) Round_ UNION ALL /* result is truncated because 3rd parameter is non-zero */
SELECT ROUND(225.715, 1) Round_ UNION ALL /* result is rounded because 3rd parameter is omitted */
SELECT ROUND(225.715, 0) Round_ UNION ALL /* result is rounded because 3rd parameter is omitted */
SELECT ROUND(225.715, -1) Round_ UNION ALL /* result is rounded because 3rd parameter is omitted */
SELECT ROUND(225.715, -2) Round_ UNION ALL /* result is rounded because 3rd parameter is omitted */
SELECT ROUND(567.655,-2) Round_ UNION ALL /* result is rounded because 3rd parameter is omitted */
SELECT ROUND(512.566,0) Round_ UNION ALL /* result is rounded because 3rd parameter is omitted */
SELECT ROUND(512.566,-1 ,0) Round_ UNION ALL /* result is truncated because 3rd parameter is non-zero */
SELECT ROUND(519.566,-1 ,2) Round_ UNION ALL /* result is truncated because 3rd parameter is non-zero */
SELECT ROUND(519.566,-4 ,2) Round_ /* result will be zero because value of Round Place is greater than total length of number */

Figure 43: Function output

SIGN(): The SIGN function returns the positive (+1), zero (0), or negative (-1) sign of the specified expression. The return type of the function is the same as the numeric expression.

Syntax: SIGN ( numeric_expression )

Figure 44: Sign

numeric_expression

Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Example

SELECT SIGN(-32.12) A1, SIGN(123.12) A2, SIGN(0.0) A3

Figure 45: Sign Output

SIN( ): The SIN function returns the trigonometric sine of the specified angle, in radians and in an approximate numeric, float, expression. The return type of the function is float.

Syntax

SIN ( float_expression )

Figure 46: Sin

float_expression: Is an expression of type float or of a type that can be implicitly converted to float.

Example

SELECT SIN(2.5) A1, SIN(-1.5) A2, SIN(3.0) A3

Figure 47: Sin Output

SQRT(): The SQRT function returns the square root of the specified float value. The return type of the function is float.

Syntax

SQRT ( float_expression )

Figure 48: Sqrt

float_expression: Is an expression of type float or of a type that can be implicitly converted to float.

Example

SELECT SQRT(9) A1,SQRT(25) A2,SQRT(45) A3

Figure 49: Sqrt output

SQUARE(): The SQUARE function returns the square of the specified float value. The return type of the function is float.

Syntax

SQUARE ( float_expression )

Figure 50: Square

float_expression

Is an expression of type float or of a type that can be implicitly converted to float.

Example

SELECT SQUARE(4) A1,SQUARE(6.3) A2, SQUARE(-9) A3

Figure 51: Square output

TAN(): The TAN function returns the tangent of the input expression. The return type of the function is float.

Syntax

TAN ( float_expression )

Figure 52: Tan

float_expression: Is an expression of type float or of a type that can be implicitly converted to float, interpreted as the number of radians.

Example