What are the numeric functions in SQL?

In this article, we will explain all about the numeric functions in SQL, how to use them, best practices, and their benefits.

What is the numeric function in SQL?

The numeric functions in SQL are a subset of SQL pre-defined functions that are designed to manipulate numeric data types and help perform calculations within the SQL queries. These functions can be used in SELECT, WHERE clauses, and in JOINS as well.

Numeric functions in SQL enable developers and data analysts to perform various mathematical operations on numeric data types as per business requirements. Common numeric functions are as follows –

ABS(), CEILING(), FLOOR(), ROUND(), EXP(), PI(), DEGREES(), RANDOM(), POWER(), RAND(), SQUARE(), SORT() etc.

Common Numeric Functions in SQL

Refer below the common numeric functions in SQL-

ABS Function in SQL

The ABS function is short for absolute value and finds the absolute (positive) values of a number in a specified column.

Syntax

SELECT ABS(number);

Example

SELECT ABS(-123) AS AbsoluteNumber;

Screenshot

In this query, the ABS function is used to get the absolute value of a given value in the SELECT list which is a negative number.

CEILING Function in SQL

The CEILING Function finds the smallest integer value that is greater or equal to (>=) a decimal number in a specified column.

Syntax

SELECT CEILING (number);

Example

SELECT CEILING(12.1) AS CelingNumber;
SELECT CEILING(12.4) AS CelingNumber;
SELECT CEILING(12.5) AS CelingNumber;
SELECT CEILING(12.8) AS CelingNumber;

Screenshot

In this query, the CEILING function is used to get the ceiling(smallest integer) value of a given value in the SELECT list which is in decimal numbers.

FLOOR Function in SQL

The FLOOR FunctionSyntax finds the largest integer value that is less or equal to (<=) a decimal number in a specified column.

Syntax

SELECT FLOOR (number);

Example

SELECT FLOOR(12.1) AS FloorNumber;
SELECT FLOOR(12.4) AS FloorNumber;
SELECT FLOOR(12.5) AS FloorNumber;
SELECT FLOOR(12.9) AS FloorNumber;

Screenshot

In this query, the FLOOR function is used to get the floor (largest integer) value of a given value in the SELECT list which is in decimal numbers.

ROUND Function in SQL

The ROUND Function finds the rounded number to a specified number or in a specified column of decimal places.

Syntax

SELECT ROUND(number, decimals)

Where the number is rounded, decimals are the number of decimal places to round numbers that consist of positive and negative signs where a positive sign indicates to move the right side from the decimal point and a negative sign indicates to move the left side from the decimal point.

Example

SELECT ROUND(1234.9234,1) AS RoundNumber;
SELECT ROUND(1234.9234,2) AS RoundNumber;
SELECT ROUND(1234.9234,3) AS RoundNumber;
SELECT ROUND(1234.9234,4) AS RoundNumber;
SELECT ROUND(1234.9234,-1) AS RoundNumber;
SELECT ROUND(1234.9234,-2) AS RoundNumber;
SELECT ROUND(1234.9234,-3) AS RoundNumber;
SELECT ROUND(1234.9234,-4) AS RoundNumber;

Screenshot

In this query, the ROUND function is used to get the rounded decimal numbers according to positive and negative signs.

EXP Function in SQL

The EXP Function short for Exponent, finds the power of a specified number or in a specified column. natural logarithms constant base is e (2.718281…).

Syntax

SELECT EXP(number);

Example

SELECT EXP(1) AS ExponentNumber;
SELECT EXP(2) AS ExponentNumber;
SELECT EXP(3) AS ExponentNumber;
SELECT EXP(4) AS ExponentNumber;
SELECT EXP(5) AS ExponentNumber;

Screenshot

In this query, the EXP function is used to get the power of specified numbers.

PI Function in SQL

The PI Function finds the value of a PI.

Syntax

SELECT PI();

Example

SELECT PI();

Screenshot

In this query, the PI function is used to get the value of PI.

DEGREES Function in SQL

The DEGREES Function, Converts radian values into the value of degrees.

Syntax

SELECT DEGREES(expression);

Example

SELECT DEGREES(1.5);
SELECT DEGREES(1);

Screenshot

In this query, the DEGREES function is used to convert the value of radian into degrees.

RADIANS Function in SQL

The RADIANS Function, Converts a degree value into the value of radians.

Syntax

SELECT RADIANS(expression);

Example

SELECT RADIANS(180);

Screenshot

In this query, the RADIANS function is used to convert the value of degree into radian.

POWER Function in SQL

The Power Function finds the value of a base number to the power of another number.

Syntax

SELECT POWER(a, b);

Where a is the base number and b is the exponent.

Example

SELECT POWER(1,2);
SELECT POWER(2,2);
SELECT POWER(3,2);
SELECT POWER(4,2);
SELECT POWER(5,2);

Screenshot

In this query, the POWER function is used to get the value of a base number to the power of another number.

RAND Numeric Function in SQL

The RAND Function is a short form of random, finding the random decimal number between 0 and 1.

Syntax

SELECT RAND(seed);

Where a seed is an optional number, if it is not provided then it gives a guaranteed random number, and if it is provided then it can give repeated random numbers.

Example

SELECT RAND(); -- To Find a Decimal Random Number (0 to 1) As seed is not provided.
SELECT RAND(6); -- To Find a Decimal Repeated Number (0 to 1) As seed is provided.
SELECT RAND()*(10-5)+5; -- To Find a Decimal Random Number. 
SELECT FLOOR(RAND()*(10-5+1)+5); -- To Find a Complete Random Number.

Screenshot

In this query, the RAND function is used to get the random decimal number.

SQUARE Numeric Function in SQL

The SQUARE Function finds the square of a number or a specified number column.

Syntax

SELECT SQUARE(number);

Example

SELECT SQUARE(8) AS SquareOf8;

Screenshot

In this query, the SQUARE function is used to get the square of a number.

SQRT Numeric Function in SQL

The SQRT Function is the short form of the square root, which finds the square root of a number or a specified number column.

Syntax

SELECT SQRT(number);

Example

SELECT SQRT(64) AS SquareRootOf64;

Screenshot

In this query, the SORT function is used to get the square root of a number.

Advantages of Using Numeric Functions in SQL

Numeric Functions offer several advantages, including enhanced query performance, streamlined data analysis, and precise calculations. Discover how integrating Numerical Functions into your SQL queries can elevate the efficiency and accuracy of your database operations.

Disadvantages of Using Numeric Functions in SQL

Despite their benefits, Numeric Functions come with limitations and considerations. From potential performance impacts to data type constraints, it’s essential to be aware of these caveats when implementing numerical functions in SQL.

Best Practices for Numeric Functions in SQL

Writing Clean and Efficient Code
Creating SQL code that is both clean and efficient is an art. Best practices for using Numeric Functions in SQL, fostering readability, maintainability, and optimal performance in your database queries.

FAQs

Conclusion

Numeric functions in SQL serve as the backbone for manipulating numeric data efficiently. Whether you’re rounding values, performing complex calculations, or aggregating data. From the foundational arithmetic operations to the advanced realms of exponents and logarithms, each function serves as a valuable tool.

We hope you like this article very well. Please share it on your social media network, which may be beneficial for others. If you have any questions, feedback, or suggestions regarding this tutorial, please contact us. Do comment right in the comments section below. We will consider your valuable input and try to give you a response ASAP.

Recommended Article Posts

What are operators in SQL?	What are Basic SQL Statements?
What are Rule and Default in SQL?	What is an index in SQL?
What is Normalization in SQL?	What are Set Operators in SQL?
What are the Functions in SQL Server?	What are Aggregate Functions in SQL Server?