Mathematical Functions in SQL: ABS, Round, Trunc, Power Function

Hi friends, If you are on this page and learning mathematical functions of SQL I believe you have visited my previous post Basics of SQL. If not, do it right now to enhance your knowledge.

There are various mathematical functions available in SQL. Some of these are given below:

  1. ABS

This mathematical function always returns a positive value.

e.g. Select ABS(-50) returns 50

2. Ceiling

This function gives the upper value of the input integer.

e.g. Select Ceiling(14.25) -> returns 15

Select (-14.25) -> returns -14

3. Floor

Floor is mathematical function which gives the nearest lowest value of the supplied input integer.

e.g. Select Floor(14.25) -> returns 14

Select Floor(-14.25) -> returns -15

4. Power

Power is mathematical function which takes integer input and returns the power of base number.

Syntax : Power(base number, power number)

e.g. Select power(3, 2) -> returns 9

5. SQRT

SQRT function returns the square root value of the input integer.

e.g. Select SQRT(81) -> returns 9

6. SQUARE()

SQUARE function returns the square value as expected from its name :p

e.g. Select SQUARE(3) -> returns 9

7. Rand()

Rand is a mathematical function which returns a random value but if you will use it in the following way: Select rand(2), then it will always return the same value.

How to generate a random value between 1 to 100?

Use floor function with rand(), this way you will be able to generate a different value each time:

Select Floor(rand()*200)

This function will return some random value between 1 and 200.

Or simply use rand()*some_integer_value

8. Round()

The round function is very useful and it rounds the supplied input value to the specified integer.

Syntax: Select Round(number_to_roundoff, int_value_to_specify_round_limit, specify 1 if value needs to be truncated)

e.g. Select round(23.456, 2, 0) – > Rounds the value up to two decimal places: 23.460

Select round(23.456, 2,1) -> Truncate the value up to two decimal places. The output will be 23.450

The round() function also takes negative value to specify the round off/trunc direction to apply. A negative value indicates the truncation/round to apply to the left.

e.g. Select round(230.456, -2) -> rounds the two integer values to the left of decimal value. Thus this function returns 200.00 as an output

Want More on Round function?

Select round(250.456, -2) -> is rounded to 300.00

Select round(250.456, -2, 1) -> value is truncated to the 2 places left to the decimal value and the output will be 200.00

Select round(256.456, -1) -> returns 260.00

Select round(256.456, -1, 1) – > returns 250.00

Hope this clarifies all the confusion about mathematical functions. Want to learn about stored procedures in SQL? Check out my post What are stored procedures and benefits of using Stored procedures.

Till that time c yaa friends 🙂 Comment on this post if you like it because this will keep me going to share good stuff with you. happy learning 🙂

4 thoughts on “Mathematical Functions in SQL: ABS, Round, Trunc, Power Function”

  1. I do not even understand how I finished up right here, however I assumed this put up was once good. I do not recognize who you might be but certainly you are going to a well-known blogger when you are not already 😉 Cheers!

Leave a Reply

Your email address will not be published. Required fields are marked *