1. Mathematical functions
In this video,
I will discuss the most common mathematical functions.
These functions are easy to learn and to remember because they are present in most programming languages. You also probably know them from school.
2. ABS(numeric_expression)
ABS(), short from absolute is a function returning the absolute value of an expression. The absolute value of a number x is defined as the non-negative value of x, without regard to its sign.
This is how we use it in SQL Server.
And these are the query results. The function always returns the positive value of a number, even if the number is negative.
3. SIGN(numeric_expression)
SIGN() is an easy function, that returns the sign of an expression.
This will be -1 if the expression is a negative number, 0 if the expression is zero and +1 if it's positive.
It has only one parameter (as you see in this query), which is the expression whose value we want to evaluate.
This is what the query returns.
4. Rounding functions
The rounding functions are used to create approximations of an expression.
CEILING() will return the smallest integer greater than or equal to the expression.
FLOOR() returns the largest integer less than or equal to an expression.
And ROUND() returns a numeric value, rounded to the specified length.
Let's see some examples for these functions.
5. Rounding functions example
This is how CEILING() is used.
For -50.49, the smallest integer larger than this value is -50 and for the positive 73.71, the CEILING() approximation is 74.
6. Rounding functions example
FLOOR() works in a similar way.
It returns the largest integer that's smaller than our expression. For -50.49 this means -51 and for 73.71, it is 73.
7. Rounding functions example
ROUND() is used to approximate a number to a specified number of decimals.
In this example, the first number is rounded to 1 decimal and the second is rounded to two decimals.
8. Exponential functions
For raising to a power or calculating the square root, there are built-in functions you can use.
POWER() receives two parameters: an expression and the power to be raised to.
If the power is always 2, you can use directly the SQUARE() function, which receives a numeric expression as parameter and returns its square value.
There is also a built-in function for calculating the square root, SQRT(). This returns the square root of a positive number.
One thing to remember when using these functions is that the numeric expression they receive as the first parameter must be a float number or an expression that can be implicitly converted to a float.
9. POWER() example
Here are some examples of calling the POWER() function with different values.
The expression can be both a positive and a negative number.
Notice that both the number and the power can be decimal numbers.
10. SQUARE() example
You can see here how SQUARE() is used.
The results will always be positive when using this function.
11. SQRT() example
If you want to calculate the square root,
this is how to do it.
You can use it for both whole and decimal numbers.
Keep in mind that SQL Server will generate an error if the expression whose square root we want to calculate is negative.
12. Let's practice!
It's now time to apply these functions with some practical examples.