SQL Server Math/Numeric Functions

Main Article :- Sql Server ABS() Function

The ABS() function returns the absolute (positive) value of the provided numeric expression.

Note: ABS converts negative to positive numbers. ABS has no impact on positive or negative numbers.

Syntax:

The ABS function in SQL Server has the following syntax:

ABS(numeric expression)

Parameter Values

Technical Details

Examples:-

Example: This example shows how to find the absolute value of a number:

SELECT ABS(-235) AS AbsValueOfNegativeNumber,
ABS(-65.96) AS AbsValueOfNegativeNumber;

Result:

The outcome demonstrates that the negative numeric value has been changed to a positive value.

The angle in radians for which the cosine is the provided float expression. Arc cosine is another name for it.

If the given value is not between -1 and 1, this function returns NULL.

Syntax:

The ACOS() function in SQL Server has the following syntax:

ACOS(float_number)

Parameter Values

Technical Details

Examples:-

Example 1: The arc cosine of an positive integer is returned in this example:

SELECT ACOS(0.75) AS PostiveFloat;

Result:

From the result, the specified number 0.75 convert into arc cosine value 0.722734247813416.

Example 2: The arc cosine of an negative integer is returned in this example:

SELECT ACOS(-0.7) AS NegativeFloat;

Result:

From the result, the specified number -0.7 convert into arc cosine value 2.34619382340565.

Example 3: Let's use zero and one in ACOS function:

SELECT ACOS(0) CosineZero,
ACOS(1) CosineOne;

Result:

When you use zero and one in the ACOS function, it returns the arccosine value that corresponds to them.

The sine of the provided float expression provides the angle in radians. Arcsine is another name for this.

If the given value is not between -1 and 1, this function returns NULL.

Syntax:

The ASIN() function in SQL Server has the following syntax:

ASIN(float_number)

Parameter Values

Technical Details

Examples:-

Example 1: The arc sine of a positive integer is returned in this example:

SELECT ASIN(0.75) AS PostiveFloat;

Result:

From the result, the specified number 0.75 convert into arc sine value 0.848062078981481.

Example 2: The arc sine of a negative integer is returned in this example:

SELECT ASIN(-0.7) AS NegativeFloat;

Result:

From the result, the specified number -0.7 convert into arc sine value -0.775397496610753.

Example 3: Let's use zero and one in ASIN function:

SELECT ASIN(0) SinZero,
ASIN(1) SinOne;

Result:

When you use zero and one in the ASIN function, it returns the arcsine value that corresponds to them.

A function that returns the angle in radians for which the tangent is a given float expression. Arctangent is another name for this.

Syntax:

The ATAN() function in SQL Server has the following syntax:

ATAN(float_number)

Parameter Values

Technical Details

Examples:-

Example 1: The arc tangent of a positive integer is returned in this example:

SELECT ATAN(0.75) AS PostiveFloat;

Result:

From the result, the specified number 0.75 convert into arc tangent value 0.643501108793284.

Example 2: The arc tangent of a negative integer is returned in this example:

SELECT ATAN(-0.7) AS NegativeFloat;

Result:

From the result, the specified number -0.7 convert into arc tangent value -0.610725964389209.

Example 3: Let's use zero and one in ASIN function:

SELECT ATAN(0) TanZero,
ATAN(1) TanOne;

Result:

When you use zero and one in the ATAN function, it returns the arctangent value that corresponds to them.

The ATN2() function returns the arc tangent of two integers.

Syntax:

The ATN2() function in SQL Server has the following syntax:

ATN2(float_number, float_number)

Parameter Values

Technical Details

Examples:-

Example 1: The ATN2 for the supplied x and y components is calculated in the following example:

SELECT ATN2(-0.80, 2);

Result:

The arc tangent of two numbers is returned as a result.

Example 2: This example to find tangent value:

SELECT ATN2(0.70, 1);

Result:

The output gives value of tangent value.

Main Article :- Sql Server CEILING() Function

The CEILING() function delivers the least integer value greater than or equal to a given number.

Syntax:

The CEILING() function in SQL Server has the following syntax:

CEILING(numeric_value)

Parameter Values

Technical Details

Examples:-

Example: This example shows positive numeric, negative numeric, and zero value inputs for the CEILING function:

SELECT CEILING(143.45) as Positive, 
CEILING(-143.45) as Negative, 
CEILING(0.0) as Zero;

Result:

From the result CEILING() function delivers the least integer value greater than or equal to a given number, if parameter is zero it will return Zero.

The trigonometric cosine of the supplied angle (calculated in radians) in the defined statement is returned by this COS() function.

Syntax:

The COS() function in SQL Server has the following syntax:

COS(float_number)

Parameter Values

Technical Details

Examples:-

Example 1: Returning the cosine of an integer is demonstrated in this example:

SELECT COS(15.75) AS PositiveCosValue, 
COS(-0.1723921) AS NegativeCosValue;

Result:

The output defines the COS values of the specified angles.

Example 2: This is an instance of the PI() value returned by the COS() functions:

SELECT COS(PI());

Result:

The PI() functions cosine value is -1.

Example 3: Let's use 1 and 0 inside cos() function:

SELECT COS(0) AS CosZero, 
COS(1) AS CosOne;

Result:

From the result cosine value of zero and 1 is returned.

The trigonometric cotangent of the supplied angle (calculated in radians) in the defined statement is returned by this COT() function.

Syntax:

The COT() function in SQL Server has the following syntax:

COT(float_number)

Parameter Values

Technical Details

Examples:-

Example 1: The COT value for a certain angle is returned in this example:

SELECT COT(3) AS CotPositiveNumber,
COT(-2) AS CotNegativeNumber,
COT(5.22) AS CotFraction;

Result:

The result used to find the cotangent value of positive, negative, and fractions.

The SQL Server DEGREES() function converts a radian angle to an approximate comparable value in degrees.

Note: RADIANS() and PI() methods are also available.

Syntax:

The DEGREES() function in SQL Server has the following syntax:

DEGREES(numeric_expr)

Parameter Values

Technical Details

Examples:-

Example 1: This example used to Convert a radian value into degrees:

SELECT DEGREES(PI()*2);

Result:

The radian value of the expression inside the function is transformed to a degree, as shown in the result.

Example 2: The example to define the value of specified radianvalue:

SELECT DEGREES(3.0);

Result:

The radian value of 3.0 converted into degree value 171.887338539246968594.

Example 3: If we use zero:

SELECT DEGREES(0);

Result:

When we use zero inside the DEGREES() function it return zero.

The EXP() function returns the exponential value of a float expression.

The constant is the base of natural logarithms (2.718281...).

Note: LOG() method is also available.

Syntax:

The EXP() function in SQL Server has the following syntax:

EXP(float_number)

Parameter Values

Technical Details

Examples:-

Example: Return expression increased to the power of 2 is an example:

SELECT EXP(3) as Expression;

Result:

The exponential value of the supplied value 3 is returned in the following result.

Main Article :- Sql Server FLOOR() Function

The FLOOR() function in SQL Server generates the greatest integer value similar to or less than a given number.

Check out the CEILING() and ROUND() methods as well.

Syntax:

The FLOOR() function in SQL Server has the following syntax:

FLOOR(float_number)

Parameter Values

Technical Details

Examples:-

Example: The FLOOR function is used in the preceding example to illustrate positive, negative, and currency values:

SELECT FLOOR(15.75) AS PositiveFloor, 
FLOOR(-15.75) AS NegativeFloor, 
FLOOR($15.75) AS CurrencyFloor;

Result:

To return the largest integer number less than or equal to 15.75, -15.75, and $15.75.

In SQL Server, LOG() function returns the natural logarithm of the provided base of a given integer.

Also take a look at the EXP() method.

Syntax:

The LOG() function in SQL Server has the following syntax:

LOG(float_number, base)

OR:

LOG(float_number)

Parameter Values

Technical Details

Examples:-

Example 1: The natural logarithms of 1 and 10 are returned in this example:

SELECT LOG(1) AS LogValueOfOne,
LOG(10) AS LogValueOfTen;

Result:

The LOG for the supplied float expression is calculated as a result.

Example 2: The LOG for a number's exponent is calculated in the following example:

SELECT LOG (EXP (15)) AS LogExpression;

Result:

The LOG of a number's exponent is calculated in the output.

The base-10 logarithm of the provided float expression is returned by the LOG10() function.

Take a look at the LOG() method as well.

Syntax:

The LOG10() function in SQL Server has the following syntax:

LOG10(float_number)

Parameter Values

Technical Details

Examples:-

Example 1: The LOG10 of the supplied Fraction value is calculated in the following example:

SELECT LOG10(7.5);

Result:

The natural logarithm of log10 is calculated as a result.

Example 2: The LOG10 of the supplied value is calculated in the following example:

SELECT LOG10(5);

Result:

The natural logarithm of log10 is calculated as a result 0.698970004336019.

The constant float value of math PI() is returned by using this SQL Server function. Although SQL Server uses the entire double-precision value implicitly, the default number of decimal places presented is seven.

Note: that the DEGREES() and RADIANS() methods are also more or less same.

Features :

• This function is used to calculate pi's value.
• There are no parameters for this function.
• This function can do any type of mathematical operation, including addition, multiplication, and division.

Syntax:

The PI() function in SQL Server has the following syntax:

PI()

Technical Details

Examples:-

Example 1: Example to return the value of PI:

SELECT PI();

Result:

The result gives value of pi.

Example 2: Obtaining the default value of pi to the third decimal place:

SELECT ROUND(PI(), 3) As PIValueDecimal;

Result:

The value of pi is returned to three decimal places as a result of this calculation.

Main Article :- Sql Server POWER() Function

The POWER() method returns the value of the input expression multiplied by the specified power.

Syntax:

The POWER() function in SQL Server has the following syntax:

POWER(numeric_value, power_value)

Parameter Values

Technical Details

Examples:-

Example 1: The example to return the second power of value:

SELECT POWER(9, 2);

Result:

The power of 8 is reutrn as 81.

Example 2: The example to check power of 3:

SELECT POWER(4.0, 3) AS CubeValue;

Result:

For the stated value of 4.0, the output is a power of 64.0.

A degree value is converted to radians using the RADIANS() function.

When a numeric expression in degrees is given, the RADIANS() method outputs radians.

Syntax:

The RADIANS() function in SQL Server has the following syntax:

RADIANS(numeric_value)

Parameter Values

Technical Details

Examples:-

Example 1: Converting a degree value to radians is demonstrated in this example:

SELECT RADIANS(-60);

Result:

The output gives degree value 30 to radian value -1.

Example 2: Convert a degree value to radians with this example:

SELECT RADIANS(210);

Result:

The degree value 210 converted into radians 3.

Main Article :- Sql Server RAND() Function

The RAND() function can be used to generate a random number or a range of random numbers between 0 (inclusive) and 1 (exclusive).

Syntax:

1. The RAND() function in SQL Server has the following syntax:

RAND(seed)

2. Syntax for Random Decimal Range:

You can use the following formula to generate a random decimal number between two values (range):

SELECT RAND()*(b-a)+a;

3. Syntax for Random Integer Range:

The following formula can be used to generate a random integer number in the range of two values:

SELECT FLOOR(RAND()*(b-a+1))+a;

Parameter Values

Technical Details

Examples:-

Example 1: To generate a random value of the RAND() function without a seed value:

SELECT RAND();

Result:

When we do not mention the seed parameters, it will return a random integer between 0 and 1.

Example 2: The example to return a range of decimal number:

SELECT RAND()*(15-10)+10;

Result:

The result returns the decimal range between 10 and 15.

Example 3: The example to return a range of integer number:

SELECT FLOOR(RAND()*(20-15+1)+15);

Result:

The result returns the integer range between 15 and 25.

Main Article :- Sql Server ROUND() Function

The ROUND() function returns an integer with a specified number of decimal places rounded off.

Look into the FLOOR() and CEILING() methods as well.

Syntax:

The ROUND() function in SQL Server has the following syntax:

ROUND(numeric_value, decimal_value, operation)

Parameter Values

Technical Details

Examples:-

Example 1: The examples below illustrate two expressions that demonstrate the use of ROUND:

SELECT ROUND(249.426, 2, 1) AS RoundValue;

Result:

Two expressions are included in the results that clarify how ROUND can be used. Always use an estimate for the last digit.

Example 2: Example of rounding a value to the -2 decimal place:

SELECT ROUND(346.56, -2) AS RoundValue;

Result:

The function will return a whole number when you use -2. It takes 10 as a base, so it will be based on the last two values before the point. And like that, when you use -2, it takes 100 as a base, so it will be based on the last three values before the point.

Example 3: Round the figure to zero decimal places in this example:

SELECT ROUND(921.5796, 0);

Result:

The result is to round the numeric value based on 0, so that all decimal places will be removed.

Example 4: used to Round the Price column (to 1 decimal):

Let's took BOOKSIMPLE table to working on following query:

The result will be look like this,

SELECT Price, 
ROUND(Price, 1) AS RoundedPrice 
FROM BOOKSIMPLE;

Result:

The result is used to round the nearest whole number based on 1 decimal in the table column.

The SIGN() function returns a value that indicates a number's sign.

One of the following values will be returned by this function:

• If number < 0, it returns 1
• If number = 0, it returns 0
• If number > 0, it returns -1

Syntax:

The SIGN() function in SQL Server has the following syntax:

SIGN(numeric_value)

Parameter Values

Technical Details

Examples:-

Example 1: The SIGN values of three numbers are returned in the following example:

SELECT SIGN(-265) AS NegativeSign, 
SIGN(0) AS Zero, 
SIGN(543) AS PositiveSign;

Result:

The result outputs a positive number, a negative number, and zero's SIGN value.

Example 2: Return the sign of a negative fractional number:

SELECT SIGN(-0.5);

Result:

The sign of negative fraction -0.5 is -1.0.

Example 3: Return the sign of a positive fractional number:

SELECT SIGN(0.5);

Result:

The sign of positive fraction 0.5 is 1.0.

The SIN() function implements the trigonometric sine of the supplied angle in radians as a numeric float expression.

Syntax:

The SIN() function in SQL Server has the following syntax:

SIN(numeric_value)

Parameter Values

Technical Details

Examples:-

Example 1: Returning the sine of an integer as an example:

SELECT SIN(-1) AS NegativeInteger,
SIN(3) AS PositiveInteger;

Result:

The sine value of negative number and positive number.

Example 2: Returning the sine of an fractional value as an example:

SELECT SIN(60.652641) AS PositiveFraction,
SIN(-35.143567) AS NegativeFraction;

Result:

The sine value of negative fraction number and positive fraction number.

Main Article :- Sql Server SQRT() Function

The SQRT() function Returns the square root of the specified float value.

Syntax:

The SQRT() function in SQL Server has the following syntax:

SQRT(numeric_value)

Parameter Values

Technical Details

Examples:-

Example 1: Returning the square root of an integer as an example:

SELECT SQRT(81);

Result:

The specified number 81 square root value is 9.

Example 2: Return the square root of a number:

SELECT SQRT(33);

Result:

The specified number 33 square root value is 5.74456264653803.

The SQUARE() function calculates the square of a float value.

Syntax:

The SQUARE() function in SQL Server has the following syntax:

SQUARE(number)

Parameter Values

Technical Details

Examples:-

Example 1: Returning the square of an integer as an example:

SELECT SQUARE(11);

Result:

The square value of 11 is 121.

Example 2: Example for return the square of a number:

SELECT SQUARE(64);

Result:

The square value of 64 is 4096.

The tangent of an integer is returned by the TAN() function.

Syntax:

The TAN() function in SQL Server has the following syntax:

TAN(numeric_value)

Parameter Values

Technical Details

Examples:-

Example 1: The following example returns the tangent of .45:

SELECT TAN(.45);

Result:

The result returns tangent value of 0.48.

Example 2: The example to return the tangent of a fractional number:

SELECT TAN(2.50);

Result:

The result returns tangent value of -0.74702229723866.

Example 3: The tangent of PI()/2 is returned in the example below:

SELECT TAN(PI()/2);

Result:

The output for Pi function in tan value 1.63312393531954E+16.