A newer version of Platform is available.

SQL Functions and Operators

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

`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`

TRUE

FALSE

TRUE

NULL

TRUE

FALSE

NULL

FALSE

NULL

NOT

a NOT a

`a`	`NOT a`
`TRUE`	`FALSE`
`FALSE`	`TRUE`
`NULL`	`NULL`

TRUE

FALSE

TRUE

NULL

IS

Predicate Description Syntax

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`

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

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);

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

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;`

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

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;`

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

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`

=

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

Operator	Description	Example
`+`	Addition	`5 + 2`
`-`	Subtraction	`5 - 2`
`*`	Multiplication	`5 * 2`
`/`	Division	`5 / 2`

+

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

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`.

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

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

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")

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

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`

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

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

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

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`

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

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

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

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)`

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)