Numeric Functions

Confluent Cloud for Apache Flink®️ provides these built-in numeric functions to use in SQL queries:

Numeric Trigonometry Random number generators Utility
ABS ACOS RAND UUID
BIN ASIN RAND(INT)  
CEILING ATAN RAND_INTEGER(INT)  
E ATAN2 RAND_INTEGER(INT1, INT2)  
EXP COS    
FLOOR COSH    
HEX COT    
LN DEGREES    
LOG RADIANS    
LOG10 SIN    
LOG2 SINH    
PI TAN    
POWER TANH    
ROUND      
SIGN      
SQRT      
TRUNCATE      

ABS

Gets the absolute value of a number.

Syntax
ABS(numeric)
Description
The ABS function returns the absolute value of the specified NUMERIC.
Examples
-- returns 23
SELECT ABS(-23);

-- returns 23
SELECT ABS(23);

ACOS

Computes the arccosine.

Syntax
ACOS(numeric)
Description
The ACOS function returns the arccosine of the specified NUMERIC.
Examples
-- returns 1.5707963267948966
-- (approximately PI/2)
SELECT ACOS(0);

-- returns 0.0
SELECT ACOS(1);

ASIN

Computes the arcsine.

Syntax
ASIN(numeric)
Description
The ASIN function returns the arcsine of the specified NUMERIC.
Examples
-- returns 0.0
SELECT ASIN(0);

-- returns 1.5707963267948966
-- (approximately PI/2)
SELECT ASIN(1);

ATAN

Computes the arctangent.

Syntax
ATAN(numeric)
Description
The ATAN function returns the arctangent of the specified NUMERIC.
Examples
-- returns 0.0
SELECT ATAN(0);

-- returns 0.7853981633974483
-- (approximately PI/4)
SELECT ATAN2(1);

ATAN2

Computes the arctangent of a 2D point.

Syntax
ATAN2(numeric1, numeric2)
Description
Returns the arctangent of the coordinate specified by (numeric1, numeric2).
Examples
-- returns 0.0
SELECT ATAN2(0, 0);

-- returns 0.7853981633974483
-- (approximately PI/4)
SELECT ATAN2(1, 1);

BIN

Converts an INTEGER number to binary.

Syntax
BIN(int)
Description
The BIN function returns a string representation of the specified INTEGER in binary format. Returns NULL if int is NULL.
Examples
-- returns "100"
SELECT BIN(4);

-- returns "1100"
SELECT BIN(12);

CEILING

Rounds a number up.

Syntax
CEILING(numeric)
Description

The CEILING function rounds the specified NUMERIC up and returns the smallest integer that’s greater than or equal to the NUMERIC.

This function can be abbreviated to CEIL(numeric).

Examples
-- returns 24
SELECT CEIL(23.55);

-- returns -23
SELECT CEIL(-23.55);

COS

Computes the cosine of an angle.

Syntax
COS(numeric)
Description
Returns the cosine of the specified NUMERIC in radians.
Examples
-- returns 1.0
SELECT COS(0);

-- returns 6.123233995736766E-17
-- (approximately 0)
SELECT COS(PI()/2);

COSH

Computes the hyperbolic cosine.

Syntax
COT(numeric)
Description
The COSH function returns the hyperbolic cosine of the specified NUMERIC. The return value type is DOUBLE.
Example
-- returns 1.0
SELECT COSH(0);

COT

Computes the cotangent of an angle.

Syntax
COT(numeric)
Description
The COT function returns the cotangent of the specified NUMERIC in radians.
Example
-- returns 6.123233995736766E-17
-- (approximately 0)
SELECT COT(PI()/2);

DEGREES

Converts an angle in radians to degrees.

Syntax
DEGREES(numeric)
Description
The DEGREES function converts the specified NUMERIC value in radians to degrees.
Examples
-- returns 90.0
SELECT DEGREES(PI()/2);

-- returns 180.0
SELECT DEGREES(PI());

-- returns -45.0
SELECT DEGREES(-PI()/4);

E

Gets the approximate value of e.

Syntax
E()
Description
Returns a value that is closer than any other values to e, the base of the natural logarithm.
Examples
-- returns 2.718281828459045
-- which is the approximate value of e
SELECT E();

-- returns 1.0
SELECT LN(E());

EXP

Computes e raised to a power.

Syntax
EXP(numeric)
Description
The EXP function returns e, the base of the natural logarithm, raised to the power of the specified NUMERIC.
Examples
-- returns 2.718281828459045
-- which is the approximate value of e
SELECT EXP(1);

-- returns 7.38905609893065
SELECT EXP(2);

-- returns 0.36787944117144233
SELECT EXP(-1);

FLOOR

Rounds a number down.

Syntax
FLOOR(numeric)
Description
The FLOOR function rounds the specified NUMERIC down and returns the largest integer that is less than or equal to the NUMERIC.
Examples
-- returns 23
SELECT FLOOR(23.55);

-- returns -24
SELECT FLOOR(-23.55);

HEX

Converts an integer or string to hexadecimal.

Syntax
HEX(numeric)
HEX(string)
Description
The HEX function returns a string representation of an integer NUMERIC value or a STRING in hexadecimal format. Returns NULL if the argument is NULL.
Examples
-- returns "14"
SELECT HEX(20);

--  returns "64"
SELECT HEX(100);

-- returns "68656C6C6F2C776F726C64"
SELECT HEX('hello,world');

LN

Computes the natural log.

Syntax
LN(numeric)
Description
The LN function returns the natural logarithm (base e) of the specified NUMERIC.
Examples
-- returns 1.0
SELECT LN(E());

-- returns 0.0
SELECT LN(1);

LOG

Computes a logarithm.

Syntax
LOG(numeric1, numeric2)
Description

The LOG function returns the logarithm of numeric2 to the base of numeric1.

When called with one argument, returns the natural logarithm of numeric2.

numeric2 must be greater than 0, and numeric1 must be greater than 1.

Examples
-- returns 1.0
SELECT LOG(10, 10);

-- returns 8.0
SELECT LOG(2, 256);

-- returns 1.0
SELECT LOG(E());

LOG10

Computes the base-10 logarithm.

Syntax
LOG10(numeric)
Description
The LOG10 function returns the base-10 logarithm of the specified NUMERIC.
Examples
-- returns 1.0
SELECT LOG10(10);

-- returns 3.0
SELECT LOG(1000);

LOG2

Computes the base-2 logarithm.

Syntax
LOG2(numeric)

Description The LOG2 function returns the base-2 logarithm of the specified NUMERIC.

Examples
-- returns 1.0
SELECT LOG2(2);

-- returns 10.0
SELECT LOG2(1024);

PI

Gets the approximate value of pi.

Syntax
PI()
Description
The PI function returns a value that is closer than any other values to pi.
Examples
-- returns 3.141592653589793
-- (approximately PI)
SELECT PI();

-- returns -1.0
SELECT COS(PI());

POWER

Raises a number to a power.

Syntax
POWER(numeric1, numeric2)
Description
The POWER function returns numeric1 raised to the power of numeric2.
Examples
-- returns 1000.0
SELECT POWER(10, 3);

-- returns 256.0
SELECT POWER(2, 8);

-- returns 1.0
SELECT POWER(500, 0);

RADIANS

Converts an angle in degrees to radians.

Syntax
RADIANS(numeric)
Description
The RADIANS function converts the specified NUMERIC value in degrees to radians.
Examples
-- returns 3.141592653589793
-- (approximately PI)
SELECT RADIANS(180);

-- returns 0.7853981633974483
-- (approximately PI/4)
SELECT RADIANS(45);

RAND

Gets a random number.

Syntax
RAND()
Description
The RAND function returns a pseudorandom DOUBLE value in the range [0.0, 1.0).
Example
-- an example return value is 0.9346105267662114
SELECT RAND();

RAND(INT)

Gets a random number from a seed.

Syntax
RAND(seed INT)
Description

The RAND(INT) function returns a pseudorandom DOUBLE value in the range [0.0, 1.0) with the initial seed integer.

Two RAND functions return identical sequences of numbers if they have the same initial seed value.

Examples
-- returns 0.7321323355141605
SELECT RAND(23);

-- returns 0.7275636800328681
SELECT RAND(42);

RAND_INTEGER(INT)

Gets a pseudorandom integer.

Syntax
RAND_INTEGER(upper_bound INT)
Description
The RAND_INTEGER(INT) functions returns a pseudorandom integer value in the range [0, upper_bound).
Examples
-- returns 20
SELECT RAND_INTEGER(23);

-- returns 28
SELECT RAND_INTEGER(42);

RAND_INTEGER(INT1, INT2)

Gets a random integer in a range.

Syntax
RAND_INTEGER(seed INT, upper_bound INT)
Description

The RAND_INTEGER(INT1, INT2) function returns a pseudorandom integer value in the range [0, upper_bound) with the initial seed value seed.

Two RAND_INTEGER functions return identical sequences of numbers if they have the same initial seed and bound.

Examples
-- returns 227
SELECT RAND_INTEGER(23, 1000);

-- returns 1130
SELECT RAND_INTEGER(42, 10000);

ROUND

Rounds a number to the specified precision.

Syntax
ROUND(numeric, int)
Description
The ROUND function returns a number rounded to int decimal places for the specified NUMERIC.
Examples
-- returns 23.6
SELECT ROUND(23.58, 1);

-- returns 3.1416
SELECT ROUND(PI(), 4);

SIGN

Gets the sign of a number.

Syntax
SIGN(numeric)
Description
The SIGN function returns the signum of the specified NUMERIC.
Examples
-- returns -1.00
SELECT SIGN(-23.55);

-- returns 1.000
SELECT SIGN(606.808);

SIN

Compute the sine of an angle.

Syntax
SIN(numeric)
Description
The SIN function returns the sine of the specified NUMERIC in radians.
Examples
-- returns 1.0
SELECT SIN(PI()/2);

-- returns -1.0
SELECT SIN(-PI()/2);

SINH

Computes the hyperbolic sine.

Syntax
SINH(numeric)
Description
The SINH function returns the hyperbolic sine of the specified NUMERIC. The return type is DOUBLE.
Example
-- returns 0.0
SELECT SINH(0);

SQRT

Computes the squre root of a number.

Syntax
SQRT(numeric)
Description
The SQRT function returns the square root of the specified NUMERIC, which must greater than or equal to 0.
Examples
-- returns 8.0
SELECT SQRT(64);

-- returns 10.0
SELECT SQRT(100);

-- returns 12.0
SELECT SQRT(144);

TAN

Computes the tangent of an angle.

Syntax
TAN(numeric)
Description
The TAN function returns the tangent of the specified NUMERIC in radians.
Examples
-- returns 0.0
SELECT TAN(0);

-- returns 0.9999999999999999
SELECT TAN(PI()/4);

TANH

Computes the hyperbolic tangent.

Syntax
TANH(numeric)
Description
The TANH function returns the hyperbolic tangent of the specified NUMERIC. The return type is DOUBLE.
Examples
-- returns 0.0
SELECT TANH(0);

-- returns 0.9999092042625951
SELECT TANH(5);

TRUNCATE

Truncates a number to the specified precision.

Syntax
TRUNCATE(numeric, integer)
Description

The TRUNCATE(numeric, integer) function returns the specified NUMERIC truncated to the number of decimal places specified by integer. Returns NULL if numeric or integer is NULL.

If integer is 0, the result has no decimal point or fractional part.

The integer value can be negative, which causes integer digits to the left of the decimal point to become zero.

If integer is not set, the function truncates as if integer were 0.

Examples
--  returns 42.32
SELECT TRUNCATE(42.324, 2);

-- returns 42.0
SELECT TRUNCATE(42.324);

-- returns 40
SELECT TRUNCATE(42.324, -1);

UUID

Generates a UUID.

Syntax
UUID()
Description

The UUID() function returns a Universally Unique Identifier (UUID) string that conforms to the RFC 4122 type 4 specification.

The UUID is generated using a cryptographically strong pseudo-random number generator.

Examples
-- an example return value is
-- 3d3c68f7-f608-473f-b60c-b0c44ad4cc4e
SELECT UUID();

Related content

Note

This website includes content developed at the Apache Software Foundation under the terms of the Apache License v2.