Categories:

Numeric Functions (Rounding and Truncation)

# CEIL¶

Returns values from `input_expr` rounded to the nearest equal or larger integer, or to the nearest equal or larger value with the specified number of places after the decimal point.

## Syntax¶

```CEIL( <input_expr> [, <scale_expr> ] )
```

## Arguments¶

`input_expr`

The value or expression to operate on. The data type should be one of the numeric data types, such as FLOAT or NUMBER.

`scale_expr`

The number of digits the output should include after the decimal point. The expression should evaluate to an integer from -38 to +38.

The default `scale_expr` is zero, meaning that the function removes all digits after the decimal point.

For information about negative scales, see the Usage Notes below.

## Returns¶

The data type of the returned value is NUMBER(precision, scale).

If the input scale was greater than or equal to zero, then the output scale generally matches the input scale.

If the input scale was negative, then the output scale is 0.

For example:
• The data type returned by CEIL(3.14, 1) is NUMBER(4, 1).

• The data type returned by CEIL(3.14, 0) is NUMBER(4, 0).

• The data type returned by CEIL(33.33, -1) is NUMBER(5, 0).

If the scale is zero, then the value is effectively an integer.

## Usage Notes¶

• If `scale_expr` is negative, then it specifies the number of places before the decimal point to which to adjust the number. For example, if the scale is -2, then the result is a multiple of 100.

• If `scale_expr` is larger than the input expression scale, the function does not have any effect.

• If either the `input_expr` or the `scale_expr` is NULL, then the result is NULL.

• When negative numbers are rounded up, the value is closer to 0; e.g. CEIL(-1.9) is -1, not -2.

• If rounding the number upward brings the number outside of the range of values of the data type, then an error is returned.

## Examples¶

This example demonstrates the function without the `scale_expr` parameter:

```SELECT CEIL(135.135), CEIL(-975.975);
+---------------+----------------+
| CEIL(135.135) | CEIL(-975.975) |
|---------------+----------------|
|           136 |           -975 |
+---------------+----------------+
```

This example demonstrates the function with the `scale_expr` parameter, including with the scale set to negative numbers:

Create and fill a table:

```CREATE TRANSIENT TABLE test_ceiling (n FLOAT, scale INTEGER);
INSERT INTO test_ceiling (n, scale) VALUES
(-975.975, -1),
(-975.975,  0),
(-975.975,  2),
( 135.135, -2),
( 135.135,  0),
( 135.135,  1),
( 135.135,  3),
( 135.135, 50),
( 135.135, NULL)
;
```

Output:

```SELECT n, scale, ceil(n, scale)
FROM test_ceiling
ORDER BY n, scale;
+----------+-------+----------------+
|        N | SCALE | CEIL(N, SCALE) |
|----------+-------+----------------|
| -975.975 |    -1 |       -970     |
| -975.975 |     0 |       -975     |
| -975.975 |     2 |       -975.97  |
|  135.135 |    -2 |        200     |
|  135.135 |     0 |        136     |
|  135.135 |     1 |        135.2   |
|  135.135 |     3 |        135.135 |
|  135.135 |    50 |        135.135 |
|  135.135 |  NULL |           NULL |
+----------+-------+----------------+
```