Numeric Functions

This page provides a comprehensive overview of Numeric functions in Databend, 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('Databend') → 3899655467