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.

You can also search these topics using sql ceil function, ceiling sql server, sql decimal format, number functions in sql.

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.

You can also search these topics using sql 2 decimal places, sql round 2 decimal places, sql round to 2 decimals, sql roundup to 2 decimal places, truncate decimal sql.