Skip to main content

Numeric Functions

SQL Numeric Functions Overview.

FunctionDescriptionExampleResult
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