SQL CEILING() | CEIL() Function
The SQL CEILING() is a function, and return next whole integer(no fractional digits) value that is equal to or greater than a given input floating number from query result.
The SQL CEILING() function will always goes to the next whole whole integer value.
Ex:
- Ceiling(4) = result is "4"
- Ceiling(2.87) = result is "3"
- Ceiling(6.12) = result is "7"
- Ceiling(1.9) = result is "2"
The SQL Ceiling() function is supports only numeric column or an numeric field based expression.
It can be used in SELECT statement as well in where clause.
Related Links
SQL CEILING() Syntax
For SQL SERVER / ORACLE / MY SQL
The basic syntax is for an numeric value or numeric expression:
SELECT CEILING(number);
The below syntax is for a given table numeric column or field from a specific table.
SELECT CEILING(Column_name) FROM table_name;
SQL CEILING() Example - Using Expression Or Formula
The following SQL SELECT statement returns the next whole integer(without fraction digits) value of a given input floating number or expression
SELECT CEILING(4.1);
The result of above query is:
| 5 |
SQL CEILING() Function More Example
| Input Value | Result |
|---|---|
| Ceiling(5.3) | 6 |
| Ceiling(6.8) | 7 |
| Ceiling(-7.7) | -7 |
| Ceiling((-4 * 1.2) + 3) | -1 |
Sample Database Table - Customer
| CID | CName | Balance | City |
|---|---|---|---|
| 111 | Suresh Babu | 128.66 | Nasik |
| 222 | Vinoth Kumar | -233.85 | Chennai |
| 333 | Azagu Varshith | 560 | Madurai |
| 444 | Haris Karthik | 673.88 | Bangalore |
SQL CEILING() Example - With Table Column
The following SQL SELECT statement find the next whole integer(without fraction digits) value of a given table column "Balance" from the "Customer" table:
SELECT CName,
CEILING(Balance) As 'New Balance'
FROM Customer;
The result of above query is:
| CName | New Balance |
|---|---|
| Suresh Babu | 129 |
| Vinoth Kumar | -233 |
| Azagu Varshith | 560 |
| Haris Karthik | 674 |
SQL CEILING() Example - Using WHERE Clause
The following SQL SELECT statement find the next whole integer value of a given input number for creating condition (greater than the given value) on table column "Balance" from the "Customer" table:
SELECT CID, CName, Balance
FROM Customer
WHERE Balance > CEILING(128.50);
The result of above query is:
| CID | CName | Balance |
|---|---|---|
| 333 | Azagu Varshith | 560 |
| 444 | Haris Karthik | 673.88 |
Note: The Ceiling(128.50) function will return a value of "129". So now the result set contains and display records or rows which has balance more than "129" on column "Balance" from the "Customer" table.
Related Links