Numeric calculation functions

This topic describes how to use the numeric calculation functions in OceanBase Database Proxy (ODP) through examples where it is assumed that the database has a table named t1 and the table contains columns c1 and c2 of the number type.

MOD

The MOD function returns the remainder after a division. ODP supports the mod(y,x) syntax, which returns the remainder of y/x.

Example: Select rows where c1 equals mod(5,2), that is, 1, from table t1. ODP can accurately route the SQL query.

select * from t1 where c1=mod(5,2);

NVL

The NVL function processes NULL values. ODP supports the nvl(expr1,expr2) syntax. That is, if expr1 is NULL, the function returns the value of expr2; otherwise, the function returns the value of expr1.

Example: Select rows where c1 equals the aaa string from table t1. ODP can accurately route the SQL query.

select * from t1 where c1=nvl('aaa',0);

We recommend that you use the following statement to process NULL values:

select * from t1 where c1 is null
# or
select * from t1 where c2 is not null

ISNULL

The ISNULL function checks whether the argument is empty. ODP supports the isnull(expr) syntax.

Example: Select rows where c1 equals isnull(mod(5,2)) from table t1. ODP can accurately route the SQL query.

select * from t1 where c1=isnull(mod(5,2));

ABS

The ABS function returns the absolute value of the argument. ODP supports the abs(number) syntax.

Example: Select rows where c1 equals abs(-100), that is, 100, from table t1. ODP can accurately route the SQL query.

select * from t1 where c1=abs(-100);

CEIL/CEILING

The CEIL and CEILING functions return the smallest integer that is greater than or equal to the argument. The following takes the CEIL function as an example.

ODP supports the ceil(number) syntax for the CEIL function.

Example: Select rows where c1 equals ceil(-7.2), that is, -7, from table t1. ODP can accurately route the SQL query.

select * from t1 where c1=ceil(-7.2);

FLOOR

The FLOOR function returns the largest integer that is less than or equal to the argument. ODP supports the floor(number) syntax.

Example: Select rows where c1 equals floor(-7.2), that is, -8, from table t1. ODP can accurately route the SQL query.

select * from t1 where c1=floor(-7.2);

TRUNCATE(MySQL)/TRUNC(Oracle)

The TRUNCATE and TRUNC functions truncate a number without rounding in MySQL mode and Oracle mode respectively. ODP supports the following syntaxes:

• truncate(number,d)

  • number: the number to be truncated.

  • d: the number of decimal places to be reserved. For a positive number, it is the number of digits to be reserved after the decimal point. For a negative number, it is the number of digits to be reserved before the decimal point.

• trunc(number,[scale])

  • number: the number to be truncated.

  • scale: the number of decimal places to be reserved. This parameter is optional. For a positive number, it is the number of digits to be reserved after the decimal point. For a negative number, it is the number of digits to be reserved before the decimal point.

Example of the TRUNCATE function: Select rows where c1 equals truncate(34.1278,3), that is, 34.127, from table t1.

select * from t1 where c1=truncate(34.1278,3);

ROUND

The ROUND function rounds a number. ODP supports the following syntaxes:

• MySQL mode: round(number,d)

  • number: the number to be rounded.

  • d: the number of decimal places to be reserved. For a positive number, it is the number of digits to be reserved after the decimal point. For a negative number, it is the number of digits to be reserved before the decimal point.

• Oracle mode: round(number,[scale])

  • number: the number to be rounded.

  • scale: the number of decimal places to be reserved. This parameter is optional. For a positive number, it is the number of digits to be reserved after the decimal point. For a negative number, it is the number of digits to be reserved before the decimal point.

Example in MySQL mode: Select rows where c1 equals round(123.123,1), that is, 123.1, from table t1.

select * from t1 where c1=round(123.123,1);