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.
-- 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
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.
-- Ceiling Function
select CEILING (125.50)
Select CEILING (-125.30)
Select CEILING (0.30)
Select CEILING (0.05)
SELECT CEILING(4.2 + 9.1)
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.
-- Floor Function
select Floor (125.50)
Select Floor (-125.30)
Select Floor (0.30)
Select Floor (0.05)
SELECT Floor(4.2 + 9.1)
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.
-- Square Function
declare @a int,@b float, @c int
set @a =4
set @b =6.1
select SQUARE (@a)
select SQUARE (@b)
select SQUARE (@c)
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
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.
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.
-- 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
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.
declare @Name Varchar(30)
SELECT ISNUMERIC (@Name) as ISNUMERICFunction
Select ISNUMERIC (5) as ISNUMERICFunction
Select ISNUMERIC (-5) as ISNUMERICFunction
Select ISNUMERIC ('Kumar') as ISNUMERICFunction
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.
-- 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.