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 return type is based on the input type:

• If the input expression is a FLOAT, the returned type is a FLOAT.

• If the input expression is a NUMBER, the returned type is a NUMBER.

• If the input scale is constant:

• If the input scale is positive, the returned type has a scale equal to the input scale and has a precision large enough to encompass any possible result.

• If the input scale is negative, the returned type has a scale of 0.

• If the input scale is not constant, the returned typeâ€™s scale is the same as the input expressionâ€™s.

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

For example:

• The data type returned by CEIL(3.14::FLOAT, 1) is FLOAT.

• The NUMBER returned by CEIL(3.14, 1) has scale 1 and precision at least 3.

• The NUMBER returned by CEIL(-9.99, 0) has scale 0 and precision at least 2.

• The NUMBER returned by CEIL(33.33, -1) has scale 0 and precision at least 3.

## 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. For example, 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 |
+----------+-------+----------------+
```
Language: English