SQL provides various numeric functions that help perform mathematical operations on numeric data. These functions are useful for calculations, rounding, and other numerical transformations.

Common Numeric Functions

• ABS(): Returns the absolute value of a number.
• CEILING(): Rounds a number up to the nearest integer.
• FLOOR(): Rounds a number down to the nearest integer.
• ROUND(): Rounds a number to a specified number of decimal places.
• POWER(): Returns the value of a number raised to a given power.
• SQRT(): Returns the square root of a number.
• EXP(): Returns the exponential value of a number.
• LOG(): Returns the natural logarithm of a number.
• LOG10(): Returns the base-10 logarithm of a number.
• RAND(): Returns a random float value between 0 and 1.
• SIGN(): Returns the sign of a number (-1, 0, or 1).
• PI(): Returns the value of PI (3.14159265358979).
• DEGREES(): Converts radians to degrees.
• RADIANS(): Converts degrees to radians.
• MOD(): Returns the remainder of a division.
• TRUNCATE(): Truncates a number to a specified decimal place.

Example Usage of Numeric Functions
1. Using ABS() Function
SELECT ABS(-15) AS AbsoluteValue;

2. Using CEILING() and FLOOR() Functions
SELECT CEILING(4.3) AS CeilValue, FLOOR(4.7) AS FloorValue;

Output

3. Using ROUND() and TRUNCATE() Functions
SELECT ROUND(123.456, 2) AS RoundedValue, TRUNCATE(123.456, 2) AS TruncatedValue;

Output

4. Using POWER() and SQRT() Functions
SELECT POWER(5, 3) AS PowerValue, SQRT(25) AS SquareRoot;

Output

5. Using MOD() Function
SELECT MOD(10, 3) AS ModResult;

6. Using PI(), DEGREES(), and RADIANS() Functions
SELECT
    PI() AS PiValue,
    DEGREES(PI()) AS DegreesValue,
    RADIANS(180) AS RadiansValue;

Output

When to Use Numeric Functions?

• Financial Calculations: Useful for interest rates, tax calculations, and rounding amounts.
• Data Analysis: Helps in statistical computations and mathematical transformations.
• Scientific Computing: Essential for performing complex mathematical calculations.
• Random Value Generation: Used for sampling, simulations, and random selections.

Advantages of Numeric Functions

• Simplifies mathematical computations in SQL.
• Enhances query efficiency by using built-in SQL functions.
• Provides precise and accurate results for calculations.

Numeric functions play a crucial role in SQL for performing various mathematical operations.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.