Mathematical Numeric Functions in SQL Server 2012

Here, you will see how to use mathematical numeric functions in SQL Server. The Mathematical Numeric functions are based on input values that are provided as arguments, and return a numeric value in SQL Server. We also covered rounding values which can cause confusion to understand and translate them to the correct data type. So let's take a look at a practical example of how to use Mathematical Numeric Functions in SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

ABS (number) Function

The ABS Function is used to ignore a negative number or value. This function returns a positive value if any value is not absolute or positive. The Numeric function abs always returns the absolute (positive) value of number.

Example

-- ABS Function

DECLARE @Test int;

SET @Test = -56765;

SELECT ABS(@Test) as absOutput;

SELECT ABS(-11.0) as absOutput, ABS(0.0) as absOutput, ABS(20.0) as absOutput

Output

CEILING (number) Function

The Ceiling Function rounds up a number to the next whole number, regardless of the decimal portion of a number. The ceil function returns the smallest integer value that is greater than or equal to the specified numeric expression.

Example

-- Ceiling Function

select CEILING (125.50)

Select CEILING (-125.30)

Select CEILING (0.30)

Select CEILING (0.05)

SELECT CEILING(4.2 + 9.1)

Output

Floor (number) Function

The Floor Function is the opposite of the ceiling function. The Floor function returns the largest integer value that is greater than or equal to the specified numeric expression or we can say that the Floor function is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Example

-- Floor Function

select Floor (125.50)

Select Floor (-125.30)

Select Floor (0.30)

Select Floor (0.05)

SELECT Floor(4.2 + 9.1)

Output

SQUARE (float_number) Function

The Square Function returns the square of the given expression. This function is an important function for when we determine something such as an area of a circle or another calculation which is based on a square. In this function, we also calculate pi()*r^2. R is the radius of circle.

Example

-- Square Function

declare @a int,@b float, @c int

set @a =4

set @b =6.1

set @c=0

select SQUARE (@a)

select SQUARE (@b)

select SQUARE (@c)

Output

Example: The following example returns the area of a circle having a radius of 5 inches.

-- Square Function with Area of circle

DECLARE @r float

SET @r = 5

SELECT PI()* SQUARE(@r) as AreaofCircle

Output

ROUND Function

The Round Function is used to round a positive or negative value to a specific length. The Round function returns the number rounded to the precision specified by length. If length is positive, the digits to the right of the decimal point are rounded. If it's negative the digits to the left of the decimal point are rounded.

Syntax

ROUND (numeric_exp, length)

Numeric expression represents the numeric value to round and the length is a positive number, numeric_expression is rounded to the number of decimal positions specified by length.

Example

-- ROUND Function

SELECT ROUND(123.9994, 3) as ROUNDFunction

Select ROUND(123.9995, 3) as ROUNDFunction

SELECT ROUND(123.4545, 2) as ROUNDFunction

Select ROUND(123.45, -2) as ROUNDFunction

SELECT ROUND(150.75, 0) as ROUNDFunction

Select ROUND(150.75, 0, 1) as ROUNDFunction

Output

IsNumeric (Expression) Function

The IsNumeric Function is used to check whether something is a valid numeric type or not. The IsNumeric function returns a value of 1 (true) if the expression is a numeric value and returns a value of 0 (false) otherwise.

Example

-- ISNUMERIC

declare @Name Varchar(30)

set @Name='Rohatash'

SELECT ISNUMERIC (@Name) as ISNUMERICFunction

Select ISNUMERIC (5) as ISNUMERICFunction

Select ISNUMERIC (-5) as ISNUMERICFunction

Select ISNUMERIC ('Kumar') as ISNUMERICFunction

Output

In the preceding example name variable that contain the string value so it returns zero value.

DEGREES (numeric_expression) Function

The Degrees Function is used to determine the angle in degrees of the specified number expression.

Example

-- Degrees Function

SELECT DEGREES(7) as DegreesFunction

SELECT DEGREES(PI()/2) as DegreesFunction

In the above example something returns an angle in degrees for a specified angle (7) in radians.

Output