MySQL - CEIL() Function

In MySQL, the CEIL() function is synonym for CEILING() function, and is used to retrieve the smallest integer value that is greater than or equal to a particular number.

This operation differs from approximation, as the value after the decimal point are neglected (unlike in approximation).

This function accepts an integer value as a parameter and returns the smallest integer not less than the given value.

Syntax

Following is the syntax of MySQL CEIL() function −

CEIL(X)

Parameters

This function takes a numeric value (either a float or a double) as a parameter.

Return Value

This function returns the smallest integer greater than or equal to the given value.

Example

The following query calculates the ceiling value (smallest integer greater than or equal to) of 22.3 −

SELECT CEIL (22.3) As Result;

Output

This will produce the following result −

Example

We can also pass a function as a value to this function as shown below. Here, the value of PI() function is 3.14 −

SELECT CEIL (PI()) As Result;

Output

The output for the query above is produced as given below −

Example

Here, we are passing a negative decimal integer value to the CEIL() function −

SELECT CEIL (-105.0238) As Result;

Output

This will produce the following result −

Example

We can also pass values to the CEIL() function as strings −

SELECT CEIL("2254.554") As Result;

Output

The output for the query above is produced as given below −

Example

In the example below, we are creating a MySQL table named CUSTOMERS using the CREATE statement as follows −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The following query inserts 7 records into the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.56 ),
(2, 'Khilan', 25, 'Delhi', 1500.33 ),
(3, 'Kaushik', 23, 'Kota', 2000.66 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.95 ),
(5, 'Hardik', 27, 'Bhopal', 8500.99 ),
(6, 'Komal', 22, 'Hyderabad', 4500.11 ),
(7, 'Muffy', 24, 'Indore', 10000.50 );

Execute the below query to fetch all the inserted records in the CUSTOMERS table −

Select * From CUSTOMERS;

Following is the CUSTOMERS table −

Now, we are using the MySQL CEIL() function on the SALARY column to return the smallest integer value that is greater than or equal to each value in the SALARY column −

SELECT ID, NAME, CEIL(SALARY) FROM CUSTOMERS;

Output

The output for the query above is produced as given below −