SQL Server Numeric Functions
Numeric Functions in SQL Server are used to perform mathematical calculations and operations on numeric data.
What are Numeric Functions?
Numeric functions are built-in SQL Server functions that are used to perform calculations and manipulate numerical values.
They are commonly used for rounding numbers, finding absolute values, calculating powers, square roots and performing other mathematical operations.
If a database contains product prices such as 125.75, 99.49 and -50, numeric functions can be used to round values, find absolute values and perform mathematical calculations.
Common Numeric Functions
| Function | Purpose | Example |
|---|---|---|
| ABS() | Returns the absolute value of a number. |
ABS(-25)
|
| CEILING() | Returns the smallest integer greater than or equal to the specified number. |
CEILING(12.4)
|
| FLOOR() | Returns the largest integer less than or equal to the specified number. |
FLOOR(12.8)
|
| ROUND() | Rounds a number to the specified number of decimal places. |
ROUND(125.678, 2)
|
| POWER() | Returns a number raised to a specified power. |
POWER(2, 3)
|
| SQRT() | Returns the square root of a number. |
SQRT(25)
|
| SIGN() | Returns the sign of a number. |
SIGN(-10)
|
| RAND() | Returns a pseudo-random floating-point value between 0 and 1. |
RAND()
|
1. ABS() Function
The ABS() function returns the absolute value of a number.
An absolute value is always positive or zero.
Syntax
ABS(number)
Example
SELECT ABS(-25);
The negative value -25 becomes 25.
2. CEILING() Function
The CEILING() function returns the smallest integer that is greater than or equal to the specified number.
Syntax
CEILING(number)
Example
SELECT CEILING(12.4);
13 is the smallest integer greater than or equal to 12.4.
3. FLOOR() Function
The FLOOR() function returns the largest integer that is less than or equal to the specified number.
Syntax
FLOOR(number)
Example
SELECT FLOOR(12.8);
4. ROUND() Function
The ROUND() function rounds a numeric value to a specified number of decimal places.
Syntax
ROUND(number, decimal_places)
Example
SELECT ROUND(125.678, 2);
The number is rounded to two decimal places.
5. POWER() Function
The POWER() function returns a number raised to the specified power.
Syntax
POWER(number, power)
Example
SELECT POWER(2, 3);
Here, 2 is raised to the power of 3: 2 × 2 × 2 = 8.
6. SQRT() Function
The SQRT() function returns the square root of a specified number.
Syntax
SQRT(number)
Example
SELECT SQRT(25);
7. SIGN() Function
The SIGN() function returns the sign of a numeric expression.
- -1 is returned for a negative number.
- 0 is returned for zero.
- 1 is returned for a positive number.
Syntax
SIGN(number)
Example
SELECT SIGN(-10);
8. RAND() Function
The RAND() function returns a pseudo-random floating-point value between 0 and 1.
Syntax
RAND()
Example
SELECT RAND();
Practical Example with a Table
Suppose we have a Products table containing product prices.
CREATE TABLE Products
(
ProductId INT,
ProductName VARCHAR(100),
Price DECIMAL(10,2)
);
Insert sample data:
INSERT INTO Products
VALUES
(1, 'Keyboard', 125.75),
(2, 'Mouse', 99.49),
(3, 'Monitor', 450.80);
Round Product Prices
SELECT
ProductName,
Price,
ROUND(Price, 0) AS RoundedPrice
FROM Products;
Find Absolute Value
SELECT
ABS(-500) AS AbsoluteValue;
Round Price Up
SELECT
ProductName,
Price,
CEILING(Price) AS CeilingPrice
FROM Products;
Round Price Down
SELECT
ProductName,
Price,
FLOOR(Price) AS FloorPrice
FROM Products;
CEILING() vs FLOOR() vs ROUND()
| Function | Example | Result |
|---|---|---|
| CEILING() | CEILING(12.4) |
13 |
| FLOOR() | FLOOR(12.8) |
12 |
| ROUND() | ROUND(12.56, 1) |
12.6 |
When Do We Use Numeric Functions?
- When mathematical calculations are required.
- When decimal values need to be rounded.
- When positive values are required from negative numbers.
- When calculating powers or square roots.
- When values need to be rounded up or down.
- When processing numerical data stored in database tables.
Important Points
- ABS() returns the absolute value.
- CEILING() rounds a value upward to an integer.
- FLOOR() rounds a value downward to an integer.
- ROUND() rounds a value to the specified decimal places.
- POWER() is used for exponent calculations.
- SQRT() returns the square root.
- SIGN() identifies whether a number is negative, zero or positive.
- RAND() generates a pseudo-random decimal value.
Summary
SQL Server Numeric Functions are used to perform mathematical operations on numeric data. In this topic, we learned ABS(), CEILING(), FLOOR(), ROUND(), POWER(), SQRT(), SIGN() and RAND(). These functions are useful when working with prices, marks, quantities, calculations and other numerical values stored in database tables.