SQL | Numeric Functions
SQL Numeric Functions are essential tools for performing mathematical and arithmetic operations on numeric data. These functions allow you to manipulate numbers, perform calculations, and aggregate data for reporting and analysis purposes. Understanding how to use SQL numeric functions is important for anyone working with data that involves calculations, such as financial, statistical, or inventory data.
In this article, we’ll dive deep into the most commonly used SQL numerical functions, providing detailed explanations, syntax, and real-world examples to help you understand how to use them effectively in your SQL queries.
What Are SQL Numeric Functions?
SQL Numeric Functions are used to perform operations on numeric data types such as INT, FLOAT, DECIMAL, DOUBLE, and others. These functions help you manipulate numbers in various ways, including performing arithmetic operations, rounding, formatting, and aggregating data.
Commonly Used SQL Numeric Functions
Following are the numeric functions defined in SQL
1. ABS() – Absolute Value
The ABS() function returns the absolute value of a number, which is the number without its sign (i.e., it converts negative numbers to positive).
Syntax:
SELECT ABS(number);
Example:
SELECT ABS(-25);
Output:
25
2. CEIL() or CEILING() – Round Number Up
The CEIL() (or CEILING()) function rounds a number up to the nearest integer, regardless of whether the decimal part is greater than or less than 0.5.
Syntax:
SELECT CEIL(number);
Example:
SELECT CEIL(12.34);
Output:
13
3. FLOOR() – Round Number Down
The FLOOR() function rounds a number down to the nearest integer, ignoring the decimal part.
Syntax:
SELECT FLOOR(number);
Example:
SELECT FLOOR(12.98);
Output:
12
4. ROUND() – Round a Number to a Specified Decimal Place
The ROUND() function rounds a number to a specified number of decimal places. It is very useful for financial calculations or whenever precise rounding is necessary.
Syntax:
SELECT ROUND(number, decimal_places);
Example:
SELECT ROUND(15.6789, 2);
Output:
15.68
5. TRUNCATE() – Remove Decimal Places
The TRUNCATE() function is used to remove the decimal portion of a number without rounding. It truncates the number to the specified number of decimal places.
Syntax:
SELECT TRUNCATE(number, decimal_places);
Example:
SELECT TRUNCATE(12.98765, 2);
Output:
12.98
6. MOD() – Modulo or Remainder
The MOD() function returns the remainder of a division operation (i.e., it computes the modulus). This function is useful for tasks like determining even/odd numbers or finding remainders in mathematical operations.
Syntax:
SELECT MOD(dividend, divisor);
Example:
SELECT MOD(10, 3);
Output:
1
7. POWER() – Raise a Number to the Power of Another
The POWER() function is used to raise a number to the power of another number. It is often used in mathematical calculations like compound interest or growth rate.
Syntax:
SELECT POWER(base, exponent);
Example:
SELECT POWER(2, 3);
Output:
8
8. SQRT() – Square Root
The SQRT() function returns the square root of a number. This is useful for mathematical calculations involving geometry or statistical analysis.
Syntax:
SELECT SQRT(number);
Example:
SELECT SQRT(16);
Output:
4
9. EXP() – Exponential Function
The EXP() function returns the value of e raised to the power of a specified number, where e is the base of the natural logarithm (approximately 2.71828).
Syntax:
SELECT EXP(number);
Example:
SELECT EXP(1);
Output:
2.718281828459045
10. LOG() – Logarithm
The LOG() function returns the natural logarithm (base e) of a number. You can also use LOG(base, number) to calculate the logarithm of a number with a custom base.
Syntax:
SELECT LOG(number);
SELECT LOG(base, number);
Example:
SELECT LOG(100);
Output:
4.605170186
11. RAND() – Random Number
The RAND() function generates a random floating-point number between 0 and 1. This function is commonly used for simulations, lotteries, or generating random samples.
Syntax:
SELECT RAND();
Example:
SELECT RAND();
Output:
0.287372
Conclusion
SQL Numeric Functions are indispensable tools for anyone working with numeric data in databases. These functions allow you to perform complex calculations, round numbers, extract specific values, and format your data for reports or analysis. By mastering SQL numeric functions like ROUND(), ABS(), MOD(), and SQRT(), you can handle a wide variety of mathematical and statistical tasks directly within your SQL queries.
