The MySQL built-in CEIL() and CEILING() functions are used for rounding up numbers with floating points to the nearest integer number.
CEIL() and CEILING() functions are synonymous, so you may prefer CEIL() because it’s shorter to type.
Both requires you to pass a number or an expression as its parameter as follows:
SELECT CEIL(2.2), CEILING(-4.4);
-- +-----------+---------------+
-- | CEIL(2.2) | CEILING(-4.4) |
-- +-----------+---------------+
-- | 3 | -4 |
-- +-----------+---------------+
You can use the two functions on both positive or negative numbers.
When you pass a string to the function, MySQL will try to interpret the string into a number if it can.
When your string can’t be interpreted to a number, the function will simply return 0 as shown below:
SELECT CEIL("7.2"), CEIL("hello");
-- +-------------+---------------+
-- | CEIL("7.2") | CEIL("hello") |
-- +-------------+---------------+
-- | 8 | 0 |
-- +-------------+---------------+
Boolean value true will be intrepreted as 1 while false will be interpreted as 0:
SELECT CEIL(true), CEIL(false);
-- +------------+-------------+
-- | CEIL(true) | CEIL(false) |
-- +------------+-------------+
-- | 1 | 0 |
-- +------------+-------------+
When you have a column of floating point numbers, you can use the CEIL() function to round up the numbers inside the column.
For example, suppose you have a scores table with the following data:
+----+-------+-------+
| id | name | score |
+----+-------+-------+
| 1 | Jack | 6.7 |
| 2 | Susan | 7.1 |
| 3 | Steve | 6.2 |
| 4 | Fany | 8.01 |
+----+-------+-------+
You can pass the score column above to the CEIL() function in your query as shown below:
SELECT score, CEIL(score) FROM scores;
-- +-------+-------------+
-- | score | CEIL(score) |
-- +-------+-------------+
-- | 6.7 | 7 |
-- | 7.1 | 8 |
-- | 6.2 | 7 |
-- | 8.01 | 9 |
-- +-------+-------------+
And that’s how you can round up numbers in MySQL using the CEIL() and CEILING() functions 👍