Numeric Functions
SQL Numeric Functions Overview.
| Function | Description | Example | Result |
|---|---|---|---|
| ABS(x) | Returns the absolute value of x. | ABS(-5) | 5 |
| ACOS(x) | Returns the arc cosine of x, that is, the value whose cosine is x. Returns NULL if x is not in the range -1 to 1. | ACOS(1) | 0.0 |
| ASIN(x) | Returns the arc sine of x, that is, the value whose sine is x. Returns NULL if x is not in the range -1 to 1. | ASIN(0.2) | 0.2013579207903308 |
| ATAN(x) | Returns the arc tangent of x, that is, the value whose tangent is x. | ATAN(-2) | -1.1071487177940906 |
| ATAN2(y, x) | Returns the arc tangent of the two variables x and y. It is similar to calculating the arc tangent of y / x, except that the signs of both arguments are used to determine the quadrant of the result. ATAN(y, x) is a synonym for ATAN2(y, x). | ATAN2(-2, 2) | -0.7853981633974483 |
| CEIL(x) | Rounds the number up. | CEIL(-1.23) | -1.0 |
| CEILING(x) | Alias for CEIL. Rounds the number up. | CEILING(1.23) | 2.0 |
| COS(x) | Returns the cosine of x, where x is given in radians. | COS(PI()) | -1.0 |
| COT(x) | Returns the cotangent of x, where x is given in radians. | COT(12) | -1.5726734063976895 |
| CRC32(x) | Returns the CRC32 checksum of a string, where x is expected to be a string and (if possible) is treated as one if it is not. | CRC32('databend') | 1177678456 |
| DEGREES(x) | Returns the argument x, converted from radians to degrees, where x is given in radians. | DEGREES(PI()) | 180 |
| EXP(x) | Returns the value of e (the base of natural logarithms) raised to the power of x. | EXP(2) | 7.38905609893065 |
| FLOOR(x) | Rounds the number down. | FLOOR(1.23) | 1.0 |
| LN(x) | Returns the natural logarithm of x; that is, the base-e logarithm of x. If x is less than or equal to 0.0E0, the function returns NULL. | LN(2) | 0.6931471805599453 |
| LOG(x) | Returns the natural logarithm of x. If x is less than or equal to 0.0E0, the function returns NULL. | LOG(2) | 0.6931471805599453 |
| LOG(b, x) | Returns the base-b logarithm of x. If x is less than or equal to 0.0E0, the function returns NULL. | LOG(2, 65536) | 16.0 |
| LOG2(x) | Returns the base-2 logarithm of x. If x is less than or equal to 0.0E0, the function returns NULL. | LOG2(65536) | 16.0 |
| LOG10(x) | Returns the base-10 logarithm of x. If x is less than or equal to 0.0E0, the function returns NULL. | LOG10(100) | 2.0 |
| PI() | Returns the value of pi as a floating-point value. | PI() | 3.141592653589793 |
| POW(x, y) | Returns the value of x to the power of y. | POW(-2, 2) | 4.0 |
| POWER(x, y) | Alias of POW. Returns the value of x to the power of y. | POWER(-2, 2) | 4.0 |
| RADIANS(x) | Returns the argument X, converted from degrees to radians. | RADIANS(90) | 1.5707963267948966 |
| RAND() | Returns a random floating-point value v in the range 0 <= v < 1.0. To obtain a random integer R in the range i <= R < j, use the expression FLOOR(i + RAND() * (j − i)). | RAND() | various |
| RAND(n) | Returns a random floating-point value v in the range 0 <= v < 1.0. To obtain a random integer R in the range i <= R < j, use the expression FLOOR(i + RAND() * (j − i)). Argument n is used as the seed value. For equal argument values, RAND(n) returns the same value each time , and thus produces a repeatable sequence of column values (last example). | RAND(1) | various |
| ROUND(x, d) | Rounds the argument x to d decimal places. The rounding algorithm depends on the data type of x. d defaults to 0 if not specified. d can be negative to cause d digits left of the decimal point of the value x to become zero. The maximum absolute value for d is 30; any digits in excess of 30 (or -30) are truncated. | ROUND(0.123, 2) | 0.12 |
| SIGN(x) | Returns the sign of the argument as -1, 0, or 1, depending on whether x is negative, zero, or positive or NULL if the argument was NULL. | SIGN(0) | 0 |
| SIN(x) | Returns the sine of x, where x is given in radians. | SIN(90) | 0.8939966636005579 |
| SQRT(x) | Returns the square root of a nonnegative number x. Return Nan for negative input. | SQRT(4) | 2.0 |
| CBRT(x) | Returns the cube root of a nonnegative number x. | CURT(27) | 3.0 |
| TAN(x) | Returns the tangent of x, where x is given in radians. | TAN(90) | -1.995200412208242 |
| TRUNCATE(x, d) | Returns the number x, truncated to d decimal places. If d is 0, the result has no decimal point or fractional part. d can be negative to cause d digits left of the decimal point of the value X to become zero. The maximum absolute value for d is 30; any digits in excess of 30 (or -30) are truncated. | TRUNCATE(1.223, 1) | 1.2 |
| FACTORIAL(x) | Returns the factorial logarithm of x. If x is less than or equal to 0, the function returns 0. | FACTORIAL(5) | 120 |