A newer version of Hazelcast Platform is available.

View latest

SQL Functions and Operators

6.0-SNAPSHOT 5.5 5.4 5.3 5.2 5.1 5.0

Hazelcast supports logical and `IS` predicates, comparison and mathematical operators, and aggregate, mathematical, trigonometric, string, table-valued, and special functions.

Operators

Operators are used to evaluate and/or compare one or more operands. For example, in the expression (a + b), the + operator evaluates the addition of the a and b operands.

AND/OR

a b a AND b a OR b

TRUE

TRUE

TRUE

TRUE

TRUE

FALSE

FALSE

TRUE

TRUE

NULL

NULL

TRUE

FALSE

FALSE

FALSE

FALSE

FALSE

NULL

FALSE

NULL

NULL

NULL

NULL

NULL

NOT

a NOT a

TRUE

FALSE

FALSE

TRUE

NULL

NULL

IS

Predicate Description Syntax

IS TRUE

Evaluates to TRUE if the boolean argument is TRUE.

WHERE expression IS TRUE

IS NOT TRUE

Evaluates to TRUE if the boolean argument is FALSE or NULL.

WHERE expression IS NOT TRUE

IS FALSE

Evaluates to TRUE if the boolean argument is FALSE.

WHERE expression IS FALSE

IS NOT FALSE

Evaluates to TRUE if the boolean argument is TRUE or NULL.

WHERE expression IS NOT FALSE

IS NULL

Evaluates to TRUE if the argument is NULL.

WHERE expression IS NULL

IS NOT NULL

Evaluates to TRUE if the argument is not NULL.

WHERE expression IS NOT NULL

IN

At the moment, the SQL service does not support subqueries in IN clauses. For example, you cannot do SELECT column_names FROM table_name WHERE column_name IN (SELECT STATEMENT);
Predicate Description Syntax

IN

Evaluates to TRUE if the value on the left of the operator is in the list of values on the right.

SELECT column_names FROM table_name WHERE column_name IN (value1, value2);

NOT IN

Evaluates to TRUE if the value on the left of the operator is not in the list of values on the right.

`SELECT column_names FROM table_name WHERE column_name NOT IN (value1, value2);

BETWEEN

Predicate Description Syntax

BETWEEN

Evaluates to TRUE if the value on the left of the operator is in a given range between the first value on the right and the second value on the right. (a >= b AND a <= c)

SELECT column_names FROM table_name WHERE column_name BETWEEN value1 AND value2;

BETWEEN SYMMETRIC

Evaluates to TRUE if the value on the left of the operator is in a given range between the first value on the right and the second value on the right or the second value on the right and the first value on the right. (a >= b AND a <= c) OR (a >= c AND a <= b).

SELECT column_names FROM table_name WHERE column_name BETWEEN SYMMETRIC value1 AND value2;

NOT BETWEEN

Evaluates to TRUE if the value on the left of the operator is not in a given range between the first value on the right and the second value on the right.

SELECT column_names FROM table_name WHERE column_name NOT BETWEEN value1 AND value2;

NOT BETWEEN SYMMETRIC

Evaluates to TRUE if the value on the left of the operator is not in a given range between the first value on the right and the second value on the right or the second value on the right and the first value on the right..

SELECT column_names FROM table_name WHERE column_name NOT BETWEEN SYMMETRIC value1 AND value2;

CASE

Predicate Description Syntax

CASE

Returns a value when the first condition is met (like an if-then-else statement).

CASE

WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN

ELSE result

END;

Comparison Operators

Operator Description Example

=

Equal to

age = 30

!= or <>

Not equal to

age != 30 or age <> 30

<

Less than

age < 30

>

Greater than

age > 30

<=

Less than or equal to

age <= 30

>=

Greater than or equal to

age >= 30

Mathematical Operators

Operator Description Example

+

Addition

5 + 2

-

Subtraction

5 - 2

*

Multiplication

5 * 2

/

Division

5 / 2

Functions

Functions can either take operands as arguments and perform calculations on them or they can take no arguments and simply perform an independant calculation. For example, the function ABS(number) takes a number and returns its absolute value.

Aggregate Functions

Aggregate functions perform calculations such as returning the mean of all data in a particular row.

You cannot aggregate data that comes a streaming query.

For examples of how to use aggregate functions, see the SELECT statement documentation.

For information about the input and return data types, see Data Types.

Function :: Returns Description

COUNT(*) :: BIGINT

Calculates the number of input rows.

COUNT(any) :: BIGINT

Calculates the number of input rows in which the input value is not null.

COUNT(DISTINCT *) :: BIGINT

Calculates the number of input rows in which the input value is unique.

COUNT(DISTINCT any) :: BIGINT

Calculates the number of input rows in which the input value is unique and not null.

SUM(TINYINT | SMALLINT | INT) :: BIGINT, SUM(BIGINT | DECIMAL) :: DECIMAL, SUM(REAL) :: REAL, SUM(DOUBLE) :: DOUBLE

Calculates the sum of the non-null input values.

SUM(DISTINCT TINYINT | SMALLINT | INT) :: BIGINT, SUM(DISTINCT BIGINT | DECIMAL) :: DECIMAL, SUM(DISTINCT REAL) :: REAL, SUM(DISTINCT DOUBLE) :: DOUBLE

Calculates the sum of the unique, non-null input values.

AVG(DECIMAL) :: DECIMAL, AVG(DOUBLE) :: DOUBLE

Calculates the mean of all the non-null input values.

AVG(DISTINCT DECIMAL) :: DECIMAL, AVG(DISTINCT DOUBLE) :: DOUBLE

Calculates the mean of all the unique, non-null input values.

MIN(any) :: same type as the input

Calculates the minimum of the non-null input values. Applicable also to OBJECT type, if the underlying value is java.lang.Comparable.

MAX(any) :: same type as the input

Calculates the maximum of the non-null input values. Applicable also to OBJECT type, if the underlying value is java.lang.Comparable.

Date and Time Functions

Function Description Example Result

EXTRACT(element, date)

Extracts element from date.

Supported elements: MILLENIUM, CENTURY, DECADE, YEAR, ISOYEAR, QUARTER, MONTH, WEEK, DAY, DOW, ISODOW, DOY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, EPOCH

Supported date types: DATE, TIME, TIMESTAMP, TIMESTAMP WITH TIME ZONE

EXTRACT(WEEK FROM "2017-06-15")

24

TO_TIMESTAMP_TZ(BIGINT)

Converts BIGINT values as seconds to TIMESTAMP_WITH_TIMEZONE.

SELECT TO_TIMESTAMP_TZ(v) FROM TABLE (generate_series(1,3))

1970-01-01T03:00:00.001+03:00

1970-01-01T03:00:00.002+03:00

1970-01-01T03:00:00.003+03:00

TO_EPOCH_MILLIS(TIMESTAMP_WITH_TIMEZONE)

Converts TIMESTAMP_WITH_TIMEZONE values to an EPOCH value in milliseconds.

SELECT TO_EPOCH_MILLIS(TO_TIMESTAMP_TZ(v)) FROM TABLE (generate_series(1,3))

1000

2000

3000

File Table Functions

To execute an ad-hoc query against data in files you can use one of the predefined table functions:

  • csv_file

  • json_flat_file

  • avro_file

  • parquet_file

Table functions will create a temporary mapping, valid for the duration of the statement. These functions accept the same options as those available for the file connector.

You can use positional arguments:

SELECT * FROM TABLE(
  CSV_FILE('/path/to/directory', '*.csv', MAP['key', 'value'])
)

Or named arguments:

SELECT * FROM TABLE(
  CSV_FILE(path => '/path/to/directory', options => MAP['key', 'value'])
)

Mathematical Functions

Function Description Example Result

ABS(number)

Absolute value of the argument

ABS(-5)

5

CBRT(number)

Returns the cube root of the input

CBRT(343)

7

CEIL(number)

Returns the nearest integer greater than or equal to argument

CEIL(25.3)

26

DEGREES(DOUBLE)

Converts radians to degrees

DEGREES(0.67)

38.38817227376516

EXP(number)

Exponential

EXP(2.5)

12.182493960703473

FLOOR(number)

Returns the nearest integer less than or equal to argument

FLOOR(25.3)

25

LN(number)

Natural logarithm

LN(2.5)

0.9162907318741551

LOG10(number)

Base 10 logarithm

LOG(2.5)

0.3979400086720376

MOD(x:number, y:number)

Returns the remainder of x / y

MOD(20,3)

2

POWER(x:number, y:number)

Returns x to the power of y

POWER(20,3)

8000

RADIANS(DOUBLE)

Converts degrees to radians

RADIANS(38.39)

0.6700318998406232

RAND

Random value in the range [0.0; 1.0)

RAND()

0.6324099982812553

RAND(number)

Random value in the range [0.0; 1.0) using the given seed

RAND(10)

0.7304302967434272

ROUND(number)

Rounds to an integer

ROUND(34.5678)

35

ROUND(number, s:integer)

Rounds to s decimal places

ROUND(34.5678, 2)

34.57

SIGN(number)

Returns -1, 0 or 1 for negative, zero or positive argument, respectively

SIGN(-25)

-1

SQUARE(number)

Squares the input

SQUARE(2)

4

SQRT(number)

Returns the square root of the input

SQRT(4)

2

TRUNCATE(number)

Truncates to an integer

TRUNC(34.5678)

34

TRUNCATE(number, s:integer)

Truncates to s decimal places

TRUNC(34.5678, 2)

34.56

Trigonometric Functions

Function Description

ACOS(double)

Inverse cosine

ASIN(double)

Inverse sine

ATAN(double)

Inverse tangent

ATAN2(x:number, y:number)

Arc tangent

COS(double)

Cosine

COT(double)

Cotangent

SIN(double)

Sine

TAN(double)

Tangent

String Functions

Function Description Example Result

string || string

Concatenates two strings

'John' || ' ' || 'Doe'

John Doe

ASCII(string)

Returns the ASCII code of the first character of the argument

ASCII('a')

97

BTRIM(string)

Equivalent to TRIM(BOTH ' ' FROM string)

CONCAT_WS(separator_string, string1, string2)

Returns a string that consists of the arguments string1 separator_string string2

CONCAT_WS('-', 'John', 'Doe')

John-Doe

INITCAP(string)

Converts the first letter of each word to upper case, and the rest to lower case

INITCAP('john DOE')

John Doe

LENGTH(string)

Length of the string

LENGTH('John Doe')

8

LIKE

Returns TRUE if the value string follows the pattern

'John Doe' LIKE '%Doe'

TRUE

NOT LIKE

Returns TRUE if the value string does not follow the pattern

'John Doe' NOT LIKE '%Doe'

FALSE

LIKE …​ ESCAPE

Returns TRUE if the value string follows the pattern, escaping a special character in the pattern

'text' LIKE '!_ext' ESCAPE '!'

FALSE

LOWER(string)

Converts the string to lower case

LOWER('John Doe')

john doe

LTRIM(string)

Equivalent to TRIM(LEADING ' ' FROM string)

POSITION(substring IN string [FROM position])

Returns the position of the first occurrence of substring in string

POSITION('Doe' IN 'John Doe')

5

REPLACE(substring, old_string, new_string)

Replaces all occurrences of substring in old_string with new_string

REPLACE('John', 'John Doe', 'Jane')

Jane Doe

RTRIM(string)

Equivalent to TRIM(TRAILING ' ' FROM string)

SUBSTRING(string FROM integer)

Extracts a substring starting with the given position

SUBSTRING('John Doe' FROM 6)

Doe

SUBSTRING(string FROM integer FOR integer)

Extracts a substring starting with the given position for the given length

SUBSTRING('John Doe' FROM 1 FOR 4)

John

TRIM([LEADING|TRAILING|BOTH] [characters FROM] string)

Removes characters (a space by default) from the start/end/both ends of the string

TRIM(BOTH '[]' FROM '[John Doe]')

John Doe

TRIM(characters FROM string)

Equivalent to TRIM(BOTH characters FROM string)

TRIM('[]' FROM '[John Doe]')

John Doe

TRIM(string)

Equivalent to TRIM(BOTH ' ' FROM string)

TRIM(' John Doe ')

John Doe

UPPER(string)

Converts a string to upper case

UPPER('John Doe')

JOHN DOE

Table-Valued Functions

Table-valued functions return tables of batch or streaming sources that you can use in SQL statements.

Function Description

TABLE(generate_series(start_number, stop_number))

Returns a table that contains a series of numbers, starting from the start_number argument and ending with the stop_number arguments.

TABLE(generate_stream(numbers_per_second))

Returns a table that contains a stream of numbers, starting from 0 at a rate of numbers_per_second every second

Special Functions

Function Description Example

CAST(value AS type)

Converts the value to the given type.

CAST(age AS VARCHAR)

NULLIF(expression1, expression2)

Returns NULL if the expressions are equal. The data type of the NULL value returned is the same as the first expression.

NULLIF(age, 18)

COALESCE( expression1 [, expression2 [, expression3 ] …​] )

Evaluates each expression at a time, starting from the first. If the value of any expression is not NULL, its value is returned. If all the expressions are NULL, NULL is returned. Arguments to the right of the first non-null argument are not evaluated.

COALESCE(NULL, NULL, NULL, 'Hazelcast', NULL, NULL)
lifebuoy

Help and support