Search
Document Center

# Mathematical functions

Last Updated: Jun 18, 2021

## ROUND

Declaration

`ROUND(X), ROUND(X,D)`

Description

This function rounds off a returned value to the specified length or precision.

The returned value `X` approximates to the nearest integer. Given two parameters, the returned value `X` is rounded off to `D` places to the right of the decimal point. To reserve `X` to `D` places to the left of the decimal point, set `D` to negative.

The return value is of the same type as that of the first independent variable (assuming that it is an integer, double-precision floating-point number, or decimal). This means that for an integer parameter, the result is also an integer (no fractional part).

• For exact-value numbers, `ROUND()` complies with the rule of "round half away from zero" or "round toward nearest". For a value whose fractional part is 0.5 or greater than 0.5, the value is rounded up to the nearest integer if it is positive and rounded down to the nearest integer if it is negative.In other words, the value is rounded away from zero. For a value whose fractional part is less than 0.5, the value is rounded down to the nearest integer if it is positive and rounded up to the nearest integer if it is negative.

• For approximate-value numbers, `ROUND()` complies with the rule of "round to nearest even": A value whose fractional part is exactly halfway between two integers is rounded to the nearest even integer.

Example

``````obclient> select round(2.15,2);
+---------------+
| round(2.15,2) |
+---------------+
|          2.15 |
+---------------+
1 row in set (0.00 sec)

obclient> select round(2555e-2,1);
+------------------+
| round(2555e-2,1) |
+------------------+
|             25.6 |
+------------------+
1 row in set (0.01 sec)

obclient> select round(25e-1), round(25.3e-1),round(35e-1);
+--------------+----------------+--------------+
| round(25e-1) | round(25.3e-1) | round(35e-1) |
+--------------+----------------+--------------+
|            2 |              3 |            4 |
+--------------+----------------+--------------+
1 row in set (0.00 sec)``````

## CEIL

Declaration

`CEIL(expr)`

Description

This function returns the smallest integer that is greater than or equal to the specified expression.

Comparison operation is also supported. The comparison result is a Boolean value, which is converted to a numeric value. The result is 1 (TRUE) or 0 (FALSE).

If you enter NULL, the return value is NULL.

If you enter a string of digits, it can be automatically converted to a numeric value.

The return value is converted to a BIGINT value.

Example

``````obclient> select ceil(1.2), ceil(-1.2), ceil(1+1.5), ceil(1=1),ceil(1<1),ceil(null);
+-----------+------------+-------------+-----------+-----------+------------+
| ceil(1.2) | ceil(-1.2) | ceil(1+1.5) | ceil(1=1) | ceil(1<1) | ceil(null) |
+-----------+------------+-------------+-----------+-----------+------------+
|         2 |         -1  |           3  |         1 |         0 |       NULL |
+-----------+------------+-------------+-----------+-----------+------------+
1 row in set (0.00 sec)

obclient> select ceil(name);
ERROR 1054 (42S22): Unknown column 'name' in 'field list'

obclient> select ceil('2');
+-----------+
| ceil('2') |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)``````

## FLOOR

Declaration

`FLOOR(expr)`

Description

This function is similar to the `CEIL(expr)` function, and returns the maximum integer smaller than or equal to the specified expression.

Comparison operation is also supported. The comparison result is a Boolean value, which is converted to a numeric value. The result is 1 (TRUE) or 0 (FALSE).

If you enter NULL, the return value is NULL.

If you enter a string of digits, it can be automatically converted to a numeric value.

The return value is converted to a BIGINT value.

Example

``````obclient> select floor(1.2), floor(-1.2), floor(1+1.5), floor(1=1),floor(1<1),floor(null);
+------------+-------------+--------------+------------+------------+-------------+
| floor(1.2) | floor(-1.2) | floor(1+1.5) | floor(1=1) | floor(1<1) | floor(null) |
+------------+-------------+--------------+------------+------------+-------------+
|                                  |               1 |          140 |
+------------+-------------+--------------+------------+------------+-------------+
1 row in set (0.00 sec)

obclient> select floor(name);
ERROR 1054 (42S22): Unknown column 'name' in 'field list'

obclient> select floor('2');
+------------+
| floor('2') |
+------------+
|          2 |
+------------+
1 row in set (0.00 sec)``````

## ABS

Declaration

`ABS(expr)`

Description

This function returns the absolute value of an expression. The return value type is the same as the data type of the numeric expression.

Comparison operation is also supported. The comparison result is a Boolean value, which is converted to a numeric value. The result is 1 (TRUE) or 0 (FALSE).

If you enter NULL, the return value is NULL.

If you enter a string of digits, it can be automatically converted to a numeric value.

The return value is converted to a BIGINT value.

Example

``````obclient> select abs(5), abs(-5.777), abs(0), abs(1/2), abs(1-5);
+--------+-------------+--------+----------+----------+
| abs(5) | abs(-5.777) | abs(0) | abs(1/2) | abs(1-5) |
+--------+-------------+--------+----------+----------+
| 5 | 5.777 | 0 | 0.5000 | 4 |
+--------+-------------+--------+----------+----------+
1 row in set (0.00 sec)``````

## NEG

Declaration

`NEG(expr)`

Description

This function performs the complement operation on the operand. It subtracts the operand from zero and then returns the result.

Comparison operation is also supported. The comparison result is a Boolean value, which is converted to a numeric value. The result is 1 (TRUE) or 0 (FALSE). The complement operation is then performed on the result.

Example

``````obclient> select neg(1), neg(1+1), neg(2*3), neg(1=1), neg(5<1);
+--------+----------+----------+----------+----------+
| neg(1) | neg(1+1) | neg(2*3) | neg(1=1) | neg(5<1) |
+--------+----------+----------+----------+----------+
|     -1 |       -2 |       -6 |       -1 |        0 |
+--------+----------+----------+----------+----------+
1 row in set (0.01 sec)``````

## SIGN

Declaration

`SIGN(X)`

Description

`SIGN(X)` returns the sign of a parameter as –1, 0, or 1, depending on the value of `X`. A negative value corresponds to the sign of –1 and a positive value corresponds to 1.

Comparison operation is also supported. The comparison result is a Boolean value, which is converted to a numeric value. The result is 1 (TRUE) or 0 (FALSE).

If you enter NULL, the return value is NULL.

Floating-point numbers and hexadecimal numbers are supported.

Example

``````obclient> SELECT SIGN(-32), SIGN(0), SIGN(234);
+-----------+---------+-----------+
| SIGN(-32) | SIGN(0) | SIGN(234) |
+-----------+---------+-----------+
|        -1 |       0 |         1 |
+-----------+---------+-----------+
1 row in set (0.01 sec)

obclient> select sign(null),sign(false),sign(0x01);
+------------+-------------+------------+
| sign(null) | sign(false) | sign(0x01) |
+------------+-------------+------------+
|       NULL |           0 |          1 |
+------------+-------------+------------+
1 row in set (0.00 sec)``````

## CONV

Declaration

`CONV(N, from_base, to_base)`

Description

This function converts numbers of one number base to another. The return value is a string converted from `from_base` to `to_base`. Input parameter `N` can be an integer or string. The minimum base is 2 and the maximum base is 36. `N` is considered a signed number if `to_base` is a negative number. Otherwise, `N` is considered an unsigned number. If `from_base` is a negative number, it is considered an integer, and the sign is ignored. The `N` parameter supports only the int and string types. The `from_base` and `to_base` parameters support only the decimal int type, and the value range is [–36,–2] U [2,36].

Invalid inputs will cause errors. Invalid inputs include:

• The `from_base` or `to_base` is not of the valid decimal int type.
• The `from_base` or `to_base` exceeds the value range of [–36,–2] U [2,36].
• `N` is not a valid numeric value. For example, the value is beyond the ranges of 0 to 9, a to z, and A to Z.

• `N` is beyond the value range of `from_base`. For example `from_base` is 2, but `N` is 3.

• `N` exceeds the maximum value range of int64: [–9223372036854775807,9223372036854775807].

Example

``````obclient> select conv(9223372036854775807,10,2);
+-----------------------------------------------------------------+
| conv(9223372036854775807,10,2) |
+-----------------------------------------------------------------+
| 111111111111111111111111111111111111111111111111111111111111111 |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

obclient> select conv('-acc',21,-7);
+--------------------+
| conv('-acc',21,-7) |
+--------------------+
| -16425 |
+--------------------+
1 row in set (0.00 sec)``````

## MOD

Declaration

`MOD(N,M)`

Description

This function returns the remainder of a number divided by another number. `MOD(N,M)`, `N % M`, and `N MOD M` are equivalent.

`MOD()` works also for numeric values with a fractional part and returns the exact remainder after the division operation.

If a parameter in `N or M` is NULL, the return value is NULL. If `M` is 0, the return value is also 0.

Example

``````obclient> select mod(29,19), 29 mod 19, 29 % 19;
+------------+-----------+---------+
| mod(29,19) | 29 mod 19 | 29 % 19 |
+------------+-----------+---------+
|         10 |        10 |      10 |
+------------+-----------+---------+
1 row in set (0.00 sec)``````

## POW

Declaration

`POW(X,Y)`

Description

This function returns the result of raising `X` to the power of `Y`.

If either of `X` or`Y` is NULL, the return value is NULL.

Example

``````obclient> select pow(4,2), pow(4,-2), pow(1,null);
+----------+-----------+-------------+
| pow(4,2) | pow(4,-2) | pow(1,null) |
+----------+-----------+-------------+
|       16 |    0.0625 |        NULL |
+----------+-----------+-------------+
1 row in set (0.00 sec)``````

## POWER

Declaration

`POWER(X,Y)`

Description

`POWER(X,Y)` and `POW(X,Y)` are synonymous functions.

## RAND

Declaration

`RAND([N])`

Description

The `RAND([N])` function accepts zero or one argument (`N` is called the random number seed) and returns a random floating-point number within the range of [0,1.0). To obtain a random integer in the range of [I, j), use the expression FLOOR(I + RAND() * (j - i)).

If the argument `N` is not specified, random number seed initialization is performed before the function is executed. Then, `RAND()` generates a random number based on this initial value. `RAND()` generates a different random number sequence each time.

If the argument `N` is specified, `N` is used as a seed to generate random numbers. Random values are generated based on whether `N` is a constant:

• If `N` is a constant, `N` it is initialized as a random number seed before execution. Then, `RAND(N)` generates a random number based on this initial value. The same `N` value will generate the same random number sequence.

• If `N` is a variable (for example, a column value), `N` is used as a random number seed to generate random numbers in each execution. The same `N` value will generate the same random number.

In addition to SECLET statements, `RAND([N])` is also used in the WHERE, ORDER BY, and GROUP BY clauses and executed based on the preceding rules. For example, to randomly sort data in a table, run the following SQL statement: `select from t1 order by rand()`. To randomly sample 100 rows from a table, run the following SQL statement: `select from t1 order by rand() limit 100`.

Example

``````obclient> select a, B, rand() from t3;
+------+------+---------------------+
| a    | b    | rand()              |
+------+------+---------------------+
|    1 |    1 |   0.641815407799385 |
|    2 |    2 | 0.16825051248841966 |
|    3 |    3 |  0.9158063697775886 |
+------+------+---------------------+
3 rows in set (0.00 sec)

obclient> select a, B, rand() from t3;
+------+------+---------------------+
| a    | b    | rand()              |
+------+------+---------------------+
|    1 |    1 | 0.07428034215632857 |
|    2 |    2 |  0.6239826321825224 |
|    3 |    3 |   0.897072165177271 |
+------+------+---------------------+
3 rows in set (0.00 sec)

obclient> select a, B, rand(3) from t3;
+------+------+---------------------+
| a    | b    | rand(3)             |
+------+------+---------------------+
|    1 |    1 |  0.9057697559760601 |
|    2 |    2 | 0.37307905813034536 |
|    3 |    3 | 0.14808605345719125 |
+------+------+---------------------+
3 rows in set (0.00 sec)

obclient> select a, B, rand(3) from t3;
+------+------+---------------------+
| a    | b    | rand(3)             |
+------+------+---------------------+
|    1 |    1 |  0.9057697559760601 |
|    2 |    2 | 0.37307905813034536 |
|    3 |    3 | 0.14808605345719125 |
+------+------+---------------------+
3 rows in set (0.00 sec)

obclient> select a, B, rand(a), rand( B) from t3;
+------+------+---------------------+---------------------+
| a    | b    | rand(a)             | rand(b)             |
+------+------+---------------------+---------------------+
|    1 |    1 | 0.40540353712197724 | 0.40540353712197724 |
|    2 |    2 |  0.6555866465490187 |  0.6555866465490187 |
|    3 |    3 |  0.9057697559760601 |  0.9057697559760601 |
+------+------+---------------------+---------------------+
3 rows in set (0.00 sec)``````