Numeric Functions
This page provides a comprehensive overview of Numeric functions in TiDB Cloud Lake, organized by functionality for easy reference.
Basic Arithmetic Functions
| Function | Description | Example |
|---|
| PLUS / ADD | Addition operator | 5 + 3 → 8 |
| MINUS / SUBTRACT | Subtraction operator | 5 - 3 → 2 |
| MULTIPLY | Multiplication operator | 5 * 3 → 15 |
| DIV | Division operator | 10 / 2 → 5.0 |
| DIV0 | Division that returns 0 instead of error for division by zero | DIV0(10, 0) → 0 |
| DIVNULL | Division that returns NULL instead of error for division by zero | DIVNULL(10, 0) → NULL |
| INTDIV | Integer division | 10 DIV 3 → 3 |
| MOD / MODULO | Modulo operation (remainder) | 10 % 3 → 1 |
| NEG / NEGATE | Negation | -5 → -5 |
Rounding and Truncation Functions
| Function | Description | Example |
|---|
| ROUND | Rounds a number to specified decimal places | ROUND(123.456, 2) → 123.46 |
| FLOOR | Returns the largest integer not greater than the argument | FLOOR(123.456) → 123 |
| CEIL / CEILING | Returns the smallest integer not less than the argument | CEIL(123.456) → 124 |
| TRUNCATE | Truncates a number to specified decimal places | TRUNCATE(123.456, 1) → 123.4 |
| TRUNC | Truncates a number to specified decimal places | TRUNC(123.456, 1) → 123.4 |
Exponential and Logarithmic Functions
| Function | Description | Example |
|---|
| EXP | Returns e raised to the power of x | EXP(1) → 2.718281828459045 |
| POW / POWER | Returns x raised to the power of y | POW(2, 3) → 8 |
| SQRT | Returns the square root of x | SQRT(16) → 4 |
| CBRT | Returns the cube root of x | CBRT(27) → 3 |
| LN | Returns the natural logarithm of x | LN(2.718281828459045) → 1 |
| LOG10 | Returns the base-10 logarithm of x | LOG10(100) → 2 |
| LOG2 | Returns the base-2 logarithm of x | LOG2(8) → 3 |
| LOGX | Returns the logarithm of y to base x | LOGX(2, 8) → 3 |
| LOGBX | Returns the logarithm of x to base b | LOGBX(8, 2) → 3 |
Trigonometric Functions
| Function | Description | Example |
|---|
| SIN | Returns the sine of x | SIN(0) → 0 |
| COS | Returns the cosine of x | COS(0) → 1 |
| TAN | Returns the tangent of x | TAN(0) → 0 |
| COT | Returns the cotangent of x | COT(1) → 0.6420926159343306 |
| ASIN | Returns the arc sine of x | ASIN(1) → 1.5707963267948966 |
| ACOS | Returns the arc cosine of x | ACOS(1) → 0 |
| ATAN | Returns the arc tangent of x | ATAN(1) → 0.7853981633974483 |
| ATAN2 | Returns the arc tangent of y/x | ATAN2(1, 1) → 0.7853981633974483 |
| DEGREES | Converts radians to degrees | DEGREES(PI()) → 180 |
| RADIANS | Converts degrees to radians | RADIANS(180) → 3.141592653589793 |
| PI | Returns the value of π | PI() → 3.141592653589793 |
Other Numeric Functions
| Function | Description | Example |
|---|
| ABS | Returns the absolute value of x | ABS(-5) → 5 |
| SIGN | Returns the sign of x | SIGN(-5) → -1 |
| FACTORIAL | Returns the factorial of x | FACTORIAL(5) → 120 |
| RAND | Returns a random number between 0 and 1 | RAND() → 0.123... (random) |
| RANDN | Returns a random number from standard normal distribution | RANDN() → -0.123... (random) |
| CRC32 | Returns the CRC32 checksum of a string | CRC32('datalake') → 2878859588 |