MSDN Documentation

MSDN > Documentation > SQL Server > Functions > Scalar Functions > Mathematical Functions

Mathematical Functions

This section describes the built-in mathematical scalar functions that can be used in SQL Server. These functions perform mathematical calculations on input values and return a single value.

Common Mathematical Functions

ABS (Transact-SQL)

ABS ( numeric_expression )

Returns the absolute value of a numeric expression.

SELECT ABS(-10.5); -- Returns 10.5

CEILING (Transact-SQL)

CEILING ( numeric_expression )

Returns the smallest integer that is greater than or equal to a specified numeric expression.

SELECT CEILING(10.3); -- Returns 11

DEGREES (Transact-SQL)

DEGREES ( numeric_expression )

Converts an angle from radians to degrees.

SELECT DEGREES(PI()); -- Returns 180

EXP (Transact-SQL)

EXP ( numeric_expression )

Returns the exponentiation of the natural logarithm of the input value. That is, e raised to the power of the input value.

SELECT EXP(1); -- Returns 2.718281828459045... (e)

FLOOR (Transact-SQL)

FLOOR ( numeric_expression )

Returns the largest integer that is less than or equal to a specified numeric expression.

SELECT FLOOR(10.7); -- Returns 10

LOG (Transact-SQL)

LOG ( [ base , ] numeric_expression )

Returns the logarithm of a numeric expression. If base is specified, it returns the logarithm of numeric_expression to the specified base. If base is not specified, it returns the natural logarithm (base e).

SELECT LOG(100); -- Returns 4.605170185988092 (natural log)
SELECT LOG(10, 100); -- Returns 2 (log base 10)

LOG10 (Transact-SQL)

LOG10 ( numeric_expression )

Returns the base-10 logarithm of a numeric expression.

SELECT LOG10(1000); -- Returns 3

PI (Transact-SQL)

PI ( )

Returns the constant value of PI.

SELECT PI(); -- Returns 3.141592653589793

POWER (Transact-SQL)

POWER ( numeric_expression , exponent )

Returns the result of raising a numeric_expression to the power of exponent.

SELECT POWER(2, 3); -- Returns 8

RADIANS (Transact-SQL)

RADIANS ( numeric_expression )

Converts an angle from degrees to radians.

SELECT RADIANS(180); -- Returns 3.141592653589793 (PI)

RAND (Transact-SQL)

RAND ( [ seed ] )

Returns a pseudo-random floating-point value between 0 and 1. An optional seed argument can be provided for reproducible results.

SELECT RAND(); -- Returns a random number between 0 and 1
SELECT RAND(123); -- Returns a repeatable random number

ROUND (Transact-SQL)

ROUND ( numeric_expression , length [ , function ] )

Rounds a numeric expression to a specified length or precision. The optional function parameter determines rounding behavior (0 or omitted for rounding, non-zero for truncation).

SELECT ROUND(123.456, 2); -- Returns 123.46
SELECT ROUND(123.456, 0); -- Returns 123.00
SELECT ROUND(123.456, -2); -- Returns 100.00
SELECT ROUND(123.456, 2, 1); -- Returns 123.45 (truncation)

SIGN (Transact-SQL)

SIGN ( numeric_expression )

Returns the sign of a number: 1 if positive, -1 if negative, and 0 if zero.

SELECT SIGN(10); -- Returns 1
SELECT SIGN(-5); -- Returns -1
SELECT SIGN(0); -- Returns 0

SIN (Transact-SQL)

SIN ( numeric_expression )

Returns the sine of an angle (in radians).

SELECT SIN(PI()/2); -- Returns 1.0

SOUNDEX (Transact-SQL)

SOUNDEX ( character_expression )

Returns a four-character code that represents the sound of a name, based on the English pronunciation. Names that sound alike are encoded to the same code.

SELECT SOUNDEX('Smith'); -- Returns S530
SELECT SOUNDEX('Smyth'); -- Returns S530

SQRT (Transact-SQL)

SQRT ( numeric_expression )

Returns the square root of a numeric expression.

SELECT SQRT(16); -- Returns 4

TAN (Transact-SQL)

TAN ( numeric_expression )

Returns the tangent of an angle (in radians).

SELECT TAN(0); -- Returns 0.0

TRUNCATE (Transact-SQL)

TRUNCATE ( numeric_expression )

Returns a number truncated to an integer. It removes the fractional part of the number.

SELECT TRUNCATE(10.75); -- Returns 10