Mathematical Functions
Table 1 lists the mathematical functions supported in DLI.
| Function | Return Type | Description |
|---|---|---|
| round(DOUBLE a) | DOUBLE | Round a. |
| round(DOUBLE a, INT d) | DOUBLE | Round a to d decimal places. Example: round(21.263,2) = 21.26. |
| bround(DOUBLE a) | DOUBLE | Round off a figure using the HALF_EVEN rounding mode. If the figure to be rounded off ends in 5, the HALF_EVEN rounding mode is as follows:
Example: bround(7.5) = 8.0, bround(6.5) = 6.0. |
| bround(DOUBLE a, INT d) | DOUBLE | Retain d decimal places and round the d+1 decimal place using the HALF_EVEN rounding mode. If the figure to be rounded off ends in 5, it will be rounded off as follows:
Example: bround(8.25, 1) = 8.2, bround(8.35, 1) = 8.4. Example: bround(8.25, 1) = 8.2, bround(8.35, 1) = 8.4. |
| floor(DOUBLE a) | BIGINT | Return the largest integer that is less than or equal to a. Example: floor(21.2) = 21. |
| ceil(DOUBLE a), ceiling(DOUBLE a) | BIGINT | Return the smallest integer that is greater than or equal to a. Example: ceil(21.2) = 22. |
| rand(), rand(INT seed) | DOUBLE | Return a random number that is distributed uniformly from 0 through 1 (1 is exclusive). If the seed is specified, a stable random number sequence is displayed. |
| exp(DOUBLE a), exp(DECIMAL a) | DOUBLE | Return the value of e raised to the power of a. |
| ln(DOUBLE a), ln(DECIMAL a) | DOUBLE | Return the natural logarithm of the argument a. |
| log10(DOUBLE a), log10(DECIMAL a) | DOUBLE | Return the base 10 logarithm of the argument a. |
| log2(DOUBLE a), log2(DECIMAL a) | DOUBLE | Return the base 2 logarithm of the argument a. |
| log(DOUBLE base, DOUBLE a) log(DECIMAL base, DECIMAL a) | DOUBLE | Return the base base logarithm of the argument a. |
| pow(DOUBLE a, DOUBLE p), power(DOUBLE a, DOUBLE p) | DOUBLE | Return the value of a raised to the power of p. |
| sqrt(DOUBLE a), sqrt(DECIMAL a) | DOUBLE | Return the square root of a. |
| bin(BIGINT a) | STRING | Return a number in binary format. |
| hex(BIGINT a) hex(STRING a) | STRING | Convert an integer or character to its hexadecimal representation. |
| conv(BIGINT num, INT from_base, INT to_base), conv(STRING num, INT from_base, INT to_base) | STRING | Convert a number from the base from_base to the base to_base. Example: Convert 5 from decimal to quaternary using conv(5,10,4) = 11. |
| abs(DOUBLE a) | DOUBLE | Return the absolute value. |
| pmod(INT a, INT b), pmod(DOUBLE a, DOUBLE b) | INT or DOUBLE | Return the positive value of the remainder after division of a by b. |
| sin(DOUBLE a), sin(DECIMAL a) | DOUBLE | Return the sine value of a. |
| asin(DOUBLE a), asin(DECIMAL a) | DOUBLE | Return the arc sine value of a. |
| cos(DOUBLE a), cos(DECIMAL a) | DOUBLE | Return the cosine value of a. |
| acos(DOUBLE a), acos(DECIMAL a) | DOUBLE | Return the arc cosine value of a. |
| tan(DOUBLE a), tan(DECIMAL a) | DOUBLE | Return the tangent value of a. |
| atan(DOUBLE a), atan(DECIMAL a) | DOUBLE | Return the arc tangent value of a. |
| degrees(DOUBLE a), degrees(DECIMAL a) | DOUBLE | Convert the value of a from radians to degrees. |
| radians(DOUBLE a), radians(DECIMAL a) | DOUBLE | Convert the value of a from degrees to radians. |
| positive(INT a), positive(DOUBLE a) | INT or DOUBLE | Return a. Example: positive(2) = 2. |
| negative(INT a), negative(DOUBLE a) | INT or DOUBLE | Return –a. Example: negative(2) = –2. |
| sign(DOUBLE a), sign(DECIMAL a) | DOUBLE or INT | Return the sign of a. 1.0 is returned if a is positive. –1.0 is returned if a is negative. Otherwise, 0.0 is returned. |
| e() | DOUBLE | Return the value of e. |
| pi() | DOUBLE | Return the value of pi. |
| factorial(INT a) | BIGINT | Return the factorial of a. |
| cbrt(DOUBLE a) | DOUBLE | Return the cube root of a. |
| shiftleft(TINYINT|SMALLINT|INT a, INT b) shiftleft(BIGINT a, INT b) | INT BIGINT | Bitwise signed left shift. Interpret a as a binary number and shift the binary number b positions to the left. |
| shiftright(TINYINT|SMALLINT|INT a, INT b) shiftright(BIGINT a, INT b) | INT BIGINT | Bitwise signed right shift. Interpret a as a binary number and shift the binary number b positions to the right. |
| shiftrightunsigned(TINYINT|SMALLINT|INT a, INT b), shiftrightunsigned(BIGINT a, INT b) | INT BIGINT | Bitwise unsigned right shift. Interpret a as a binary number and shift the binary number b positions to the right. |
| greatest(T v1, T v2, ...) | T | Return the maximum value of a list of values. |
| least(T v1, T v2, ...) | T | Return the minimum value of a list of values. |
Last Article: Built-in Functions
Next Article: Date Functions
Did this article solve your problem?
Thank you for your score！Your feedback would help us improve the website.