Conditional Functions in Confluent Cloud for Apache Flink

Confluent Cloud for Apache Flink® provides these built-in functions for controlling execution flow in SQL queries:

CASE CASE WHEN CONDITION COALESCE
GREATEST IF IFNULL
IS_ALPHA IS_DECIMAL IS_DIGIT
LEAST NULLIF  

CASE

Syntax
CASE value
  WHEN value1_1 [, value1_2]* THEN result1
  (WHEN value2_1 [, value2_2 ]* THEN result2)*
  (ELSE result_z)
END
Copy
Description
Returns resultX when the specified value is contained in (valueX_1, valueX_2, ...). If no value matches, CASE returns result_z, if it’s provided, otherwise NULL.

CASE WHEN CONDITION

Syntax
CASE
  WHEN condition1 THEN result1
  (WHEN condition2 THEN result2)*
  (ELSE result_z)
END
Copy

Returns resultX when the first conditionX is met. When no condition is met, returns result_z, if it’s provided, otherwise NULL.

COALESCE

Syntax
COALESCE(value1 [, value2]*)
Copy

Returns the first argument that is not NULL.

If all arguments are NULL, the COALESCE function returns NULL.

The return type is the least-restrictive, common type of all the arguments.

The return type is nullable if all arguments are nullable as well.

Example

The following SELECT statements return the values indicated in the comment lines.

-- Returns 'default'
SELECT COALESCE(NULL, 'default');

-- Returns the first non-null value among column0 and column1,
-- or 'default' if column0 and column1 are both NULL.
SELECT COALESCE(column0, column1, 'default');
Copy

GREATEST

Syntax
GREATEST(value1[, value2]*)
Copy

Returns the greatest value in the specified list of arguments. Returns NULL if any argument is NULL.

Example
-- returns 4
SELECT GREATEST(1, 2, 3, 4);

-- returns d
SELECT GREATEST('a', 'b', 'c', 'd');
Copy

IF

Syntax
IF(condition, true_value, false_value)
Copy

Returns the true_value if condition is met, otherwise false_value.

Example

The following SELECT statements return the values indicated in the comment lines.

-- returns 5
SELECT IF(5 > 3, 5, 3);
Copy

IFNULL

Syntax
IFNULL(input, null_replacement)
Copy

Returns null_replacement if input is NULL; otherwise returns input.

The IFNULL function enables passing nullable columns into a function or table that is declared with a NOT NULL constraint.

Compared with COALESCE or CASE, the IFNULL function returns a data type that’s specific with respect to nullability. The returned type is the common type of both arguments but only nullable if the null_replacement is nullable.

For example, IFNULL(nullable_column, 5) never returns NULL.

IS_ALPHA

Syntax
IS_ALPHA(string)
Copy

Returns TRUE if all characters in the specified string are alphabetic, otherwise FALSE.

Example
-- returns FALSE
SELECT IS_ALPHA('42');

-- returns TRUE
SELECT IS_ALPHA('string');
Copy

IS_DECIMAL

Syntax
IS_DECIMAL(string)
Copy

Returns TRUE if the specified string can be parsed to a valid NUMERIC, otherwise FALSE.

Example
-- returns TRUE
SELECT IS_DECIMAL('23');

-- returns FALSE
SELECT IS_DECIMAL('not a number');
Copy

IS_DIGIT

Syntax
IS_DIGIT(string)
Copy

Returns TRUE if all characters in the specified string are digits, otherwise FALSE.

Example
-- returns TRUE
SELECT IS_DIGIT('23');

-- returns FALSE
SELECT IS_DIGIT('2 not a digit 3');
Copy

LEAST

Syntax
LEAST(value1[, value2]*)
Copy

Returns the lowest value in the specified list of arguments. Returns NULL if any argument is NULL.

Example
-- returns 1
SELECT LEAST(1, 2, 3, 4);

-- returns a
SELECT LEAST('a', 'b', 'c', 'd');
Copy

NULLIF

Syntax
NULLIF(value1, value2)
Copy
Description
Returns NULL if value1 is equal to value2, otherwise returns value1.
Example
-- returns NULL
SELECT NULLIF(5, 5);

-- returns 5
SELECT NULLIF(5, 0);
Copy

Related content

Note

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