How the CEIL() function works in Mariadb?

The CEIL() function is a mathematical function that returns the smallest integer value that is greater than or equal to a given numeric value.

Posted on

The CEIL() function is a mathematical function that returns the smallest integer value that is greater than or equal to a given numeric value. The CEIL() function can be used to round up a value to the nearest integer, or to get the ceiling of a value.

Syntax

The syntax of the CEIL() function is as follows:

CEIL(value)

Where:

  • value is a numeric expression that returns a value to be rounded up.

The return type of the function is an integer value.

Examples

Example 1: Rounding up a decimal value

In this example, we use the CEIL() function to round up a decimal value to the nearest integer. We use the SELECT statement to display the result.

SELECT CEIL(3.14) AS ceil;

The output is:

+------+
| ceil |
+------+
| 4    |
+------+

Example 2: Rounding up a negative value

In this example, we use the CEIL() function to round up a negative value to the nearest integer. We use the SELECT statement to display the result.

SELECT CEIL(-3.14) AS ceil;

The output is:

+------+
| ceil |
+------+
| -3   |
+------+

Example 3: Rounding up a column value

In this example, we use the CEIL() function to round up a column value to the nearest integer. We use the products table as an example, which has the following structure and data:

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(10,2)
);

INSERT INTO products VALUES
(1, 'Laptop', 999.99),
(2, 'Mouse', 19.99),
(3, 'Keyboard', 49.99),
(4, 'Monitor', 199.99),
(5, 'Speaker', 29.99);

We use the SELECT statement to display the product name and the rounded up price.

SELECT name, CEIL(price) AS rounded_price
FROM products;

The output is:

+---------+--------------+
| name    | rounded_price|
+---------+--------------+
| Laptop  | 1000         |
| Mouse   | 20           |
| Keyboard| 50           |
| Monitor | 200          |
| Speaker | 30           |
+---------+--------------+

Some other functions that are related to the CEIL() function are:

  • FLOOR(): Returns the largest integer value that is less than or equal to a given numeric value. The syntax is FLOOR(value).
  • ROUND(): Returns a numeric value rounded to a specified number of decimal places. The syntax is ROUND(value, decimals).
  • TRUNCATE(): Returns a numeric value truncated to a specified number of decimal places. The syntax is TRUNCATE(value, decimals).

For example, you can use the FLOOR() function to achieve the opposite result of the CEIL() function:

SELECT FLOOR(3.14) AS floor;

The output is:

+-------+
| floor |
+-------+
| 3     |
+-------+

Conclusion

The CEIL() function is a useful function to round up a numeric value to the nearest integer, or to get the ceiling of a value. The CEIL() function takes a numeric expression as an argument, and returns an integer value that is greater than or equal to the argument. The CEIL() function can be used in various contexts, such as in SELECT, UPDATE, DELETE, WHERE, ORDER BY, and GROUP BY clauses. The CEIL() function can be combined with other functions to perform various calculations and operations.