# Comparison Functions in Confluent Cloud for Apache Flink Confluent Cloud for Apache Flink® provides these built-in comparison functions to use in SQL queries: - [Equality operations](#flink-sql-equality-operations) - [Logical operations](#flink-sql-logical-operations) - [Comparison Functions](#flink-sql-comparison-functions) - [Conversion functions](#flink-sql-conversion-functions) ## Equality operations | SQL function | Description | |--------------------|---------------------------------------------------------------------------------------------------------| | `value1 = value2` | Returns TRUE if value1 is equal to value2. Returns UNKNOWN if value1 or value2 is NULL. | | `value1 <> value2` | Returns TRUE if value1 is not equal to value2. Returns UNKNOWN if value1 or value2 is NULL. | | `value1 > value2` | Returns TRUE if value1 is greater than value2. Returns UNKNOWN if value1 or value2 is NULL. | | `value1 >= value2` | Returns TRUE if value1 is greater than or equal to value2. Returns UNKNOWN if value1 or value2 is NULL. | | `value1 < value2` | Returns TRUE if value1 is less than value2. Returns UNKNOWN if value1 or value2 is NULL. | | `value1 <= value2` | Returns TRUE if value1 is less than or equal to value2. Returns UNKNOWN if value1 or value2 is NULL. | ## Logical operations | Logical operation | Description | |--------------------------|---------------------------------------------------------------------------------------------------------------------------------------------| | `boolean1 OR boolean2` | Returns TRUE if `boolean1` is TRUE or `boolean2` is TRUE. Supports three-valued logic. For example, `TRUE || NULL(BOOLEAN)` returns TRUE. | | `boolean1 AND boolean2` | Returns TRUE if `boolean1` and `boolean2` are both TRUE. Supports three-valued logic. For example, `TRUE && NULL(BOOLEAN)` returns UNKNOWN. | | `NOT boolean` | Returns TRUE if `boolean` is FALSE; returns FALSE if `boolean` is TRUE; returns UNKNOWN if boolean is UNKNOWN. | | `boolean IS FALSE` | Returns TRUE if `boolean` is FALSE; returns FALSE if `boolean` is TRUE or UNKNOWN. | | `boolean IS NOT FALSE` | Returns TRUE if `boolean` is TRUE or UNKNOWN; returns FALSE if `boolean` is FALSE. | | `boolean IS TRUE` | Returns TRUE if `boolean` is TRUE; returns FALSE if `boolean` is FALSE or UNKNOWN. | | `boolean IS NOT TRUE` | Returns TRUE if `boolean` is FALSE or UNKNOWN; returns FALSE if `boolean` is TRUE. | | `boolean IS UNKNOWN` | Returns TRUE if `boolean` is UNKNOWN; returns FALSE if `boolean` is TRUE or FALSE. | | `boolean IS NOT UNKNOWN` | Returns TRUE if `boolean` is TRUE or FALSE; returns FALSE if `boolean` is UNKNOWN. | ## Comparison functions | [BETWEEN](#flink-sql-between-function) | [NOT BETWEEN](#flink-sql-not-between-function) | |----------------------------------------------------------|------------------------------------------------------------------| | [IN](#flink-sql-in-function) | [NOT IN](#flink-sql-not-in-function) | | [IS DISTINCT FROM](#flink-sql-is-distinct-from-function) | [IS NOT DISTINCT FROM](#flink-sql-is-not-distinct-from-function) | | [IS NULL](#flink-sql-is-null-function) | [IS NOT NULL](#flink-sql-is-not-null-function) | | [LIKE](#flink-sql-like-function) | [NOT LIKE](#flink-sql-not-like-function) | | [SIMILAR TO](#flink-sql-similar-to-function) | [NOT SIMILAR TO](#flink-sql-not-similar-to-function) | | [EXISTS](#flink-sql-exists-function) | | ### BETWEEN Checks whether a value is between two other values. Syntax : ```sql value1 BETWEEN [ ASYMMETRIC | SYMMETRIC ] value2 AND value3 ``` Description : The `BETWEEN` function returns TRUE if `value1` is greater than or equal to `value2` and less than or equal to `value3`, if ASYMMETRIC is specified. The default is ASYMMETRIC.
If SYMMETRIC is specified, the `BETWEEN` function returns TRUE if `value1` is *inclusively* between `value2` and `value3`.
When either `value2` or `value3` is NULL, returns FALSE or UNKNOWN. Examples : ```sql - returns FALSE SELECT 12 BETWEEN 15 AND 12;
- returns TRUE SELECT 12 BETWEEN SYMMETRIC 15 AND 12;
- returns UNKNOWN SELECT 12 BETWEEN 10 AND NULL;
- returns FALSE SELECT 12 BETWEEN NULL AND 10;
- returns UNKNOWN SELECT 12 BETWEEN SYMMETRIC NULL AND 12; ``` ### NOT BETWEEN Checks whether a value is not between two other values. Syntax : ```sql value1 NOT BETWEEN [ ASYMMETRIC | SYMMETRIC ] value2 AND value3 ``` Description : By default, or with the ASYMMETRIC keyword, the `NOT BETWEEN` function returns TRUE if `value1` is less than `value2` or greater than `value3`.
If SYMMETRIC is specified, the `NOT BETWEEN` function returns TRUE if `value1` is not inclusively between `value2` and `value3`.
When either `value2` or `value3` is NULL, returns TRUE or UNKNOWN. Examples : ```sql -- returns TRUE SELECT 12 NOT BETWEEN 15 AND 12;
-- returns FALSE SELECT 12 NOT BETWEEN SYMMETRIC 15 AND 12;
-- returns UNKNOWN SELECT 12 NOT BETWEEN NULL AND 15;
-- returns TRUE SELECT 12 NOT BETWEEN 15 AND NULL;
-- returns UNKNOWN SELECT 12 NOT BETWEEN SYMMETRIC 12 AND NULL; ``` ### EXISTS Check whether a query returns a row. Syntax : ```sql EXISTS (sub-query) ``` Description : The `EXISTS` function returns TRUE if `sub-query` returns at least one row.
The `EXISTS` function is supported only if the operation can be rewritten in a join and group operation.
For streaming queries, the operation is rewritten in a join and group operation.
The required state to compute the query result might grow indefinitely, depending on the number of distinct input rows. Provide a query configuration with valid retention interval to prevent excessive state size. Examples : ```sql SELECT user_id, item_id FROM user_behavior WHERE EXISTS ( SELECT * FROM category WHERE category.item_id = user_behavior.item_id AND category.name = 'book' ); ``` ### IN Checks whether a value exists in a list. Syntax : ```sql value1 IN (value2 [, value3]* ) value IN (sub-query) ``` Description : The `IN` function returns TRUE if `value1` exists in the specified list `(value2, value3, ...)`.
If a subquery is specified, the `IN` function returns TRUE if `value` is equal to a row returned by `sub-query`.
When `(value2, value3, ...)` contains NULL, The `IN` function returns TRUE if the element can be found and UNKNOWN otherwise.
Always returns UNKNOWN if `value1` is NULL. Examples : ```sql -- returns FALSE SELECT 4 IN (1, 2, 3);
-- returns TRUE SELECT 1 IN (1, 2, NULL);
-- returns UNKNOWN SELECT 4 IN (1, 2, NULL); ``` ### NOT IN Checks whether a value doesn’t exist in a list. Syntax : ```sql value1 NOT IN (value2 [, value3]* ) value NOT IN (sub-query) ``` Description : The `NOT IN` function returns TRUE if `value1` does not exist in the specified list `(value2, value3, ...)`.
If a subquery is specified, the `NOT IN` function returns TRUE if `value` isn’t equal to a row returned by `sub-query`.
When `(value2, value3, ...)` contains NULL, the `NOT IN` function returns FALSE if `value1` can be found and UNKNOWN otherwise.
Always returns UNKNOWN if value1 is NULL. Examples : ```sql -- returns TRUE SELECT 4 NOT IN (1, 2, 3);
-- returns FALSE SELECT 1 NOT IN (1, 2, NULL);
-- returns UNKNOWN SELECT 4 NOT IN (1, 2, NULL); ``` ### IS DISTINCT FROM Checks whether two values are different. Syntax : ```sql value1 IS DISTINCT FROM value2 ``` Description : The `IS DISTINCT FROM` function returns TRUE if two values are different.
NULL values are treated as identical. Examples : ```sql -- returns TRUE SELECT 1 IS DISTINCT FROM 2;
-- returns TRUE SELECT 1 IS DISTINCT FROM NULL;
-- returns FALSE SELECT NULL IS DISTINCT FROM NULL; ``` ### IS NOT DISTINCT FROM Checks whether two values are equal. Syntax : ```sql value1 IS NOT DISTINCT FROM value2 ``` Description : The `IS NOT DISTINCT FROM` function returns TRUE if two values are equal.
NULL values are treated as identical. Examples : ```sql -- returns FALSE SELECT 1 IS NOT DISTINCT FROM 2;
-- returns FALSE SELECT 1 IS NOT DISTINCT FROM NULL;
-- returns TRUE SELECT NULL IS NOT DISTINCT FROM NULL; ``` ### IS NULL Checks whether a value is NULL. Syntax : ```sql value IS NULL ``` Description : The `IS NULL` function returns TRUE if `value` is NULL. Examples : ```sql -- returns FALSE SELECT 1 IS NULL;
-- returns TRUE SELECT NULL IS NULL; ``` ### IS NOT NULL Checks whether a value is assigned. Syntax : ```sql value IS NOT NULL ``` Description : The `IS NOT NULL` function returns TRUE if `value` is not NULL. Examples : ```sql -- returns TRUE SELECT 1 IS NOT NULL;
-- returns FALSE SELECT NULL IS NOT NULL; ``` ### LIKE Checks whether a string matches a pattern. Syntax : ```sql string1 LIKE string2 [ ESCAPE char ] ``` Description : The `LIKE` function returns TRUE if `string1` matches the pattern specified by `string2`.
An escape character can be provided. The default is `\`.
The pattern can contain these special characters:
- **%** – matches any number of characters - **\_** – matches a single character
Returns UNKNOWN if either `string1` or `string2` is NULL. Examples : ```sql -- returns TRUE SELECT 'book-23' LIKE 'book-%';
-- returns FALSE SELECT 'book23' LIKE 'book_';
-- returns TRUE SELECT 'book2' LIKE 'book_'; ``` ### NOT LIKE Checks whether a string matches a pattern. Syntax : ```sql string1 NOT LIKE string2 [ ESCAPE char ] ``` Description : The `NOT LIKE` function returns TRUE if `string1` does not match the pattern specified by `string2`.
The pattern can contain these special characters:
- **%** – matches any number of characters - **\_** – matches a single character
Returns UNKNOWN if `string1` or `string2` is NULL.
An escape character can be provided. The default is `\`. Examples : ```sql -- returns FALSE SELECT 'book-23' NOT LIKE 'book-%';
-- returns TRUE SELECT 'book23' NOT LIKE 'book_';
-- returns FALSE SELECT 'book2' NOT LIKE 'book_'; ``` ### SIMILAR TO Checks whether a string matches a regular expression. Syntax : ```sql string1 SIMILAR TO string2 ``` Description : The `SIMILAR TO` function returns TRUE if `string1` matches the SQL regular expression in `string2`.
The pattern can contain any characters that are valid in regular expressions, like `.`, which matches any character, `*`, which matches zero or more occurrences, and `+` which matches one or more occurrences.
Returns UNKNOWN if `string1` or `string2` is NULL. Examples : ```sql -- returns TRUE SELECT 'book-523' SIMILAR TO 'book-[0-9]+';
-- returns TRUE SELECT 'bob.dobbs@example.com' SIMILAR TO '%@example.com'; ``` ### NOT SIMILAR TO Checks whether a string doesn’t match a regular expression. Syntax : ```sql string1 NOT SIMILAR TO string2 [ ESCAPE char ] ``` Description : The `NOT SIMILAR TO` function returns TRUE if `string1` does not match the SQL regular expression specified by `string2`.
Returns UNKNOWN if `string1` or `string2` is NULL. Examples : ```sql -- returns TRUE SELECT 'book-nan' NOT SIMILAR TO 'book-[0-9]+';
-- returns TRUE SELECT 'bob.dobbs@company.com' NOT SIMILAR TO '%@example.com'; ``` ## Conversion functions - [CAST](#flink-sql-cast-function) - [IS_VALID_UTF8](#flink-sql-is-valid-utf8-function) - [MAKE_VALID_UTF8](#flink-sql-make-valid-utf8-function) - [TRY_CAST](#flink-sql-try-cast-function) - [TYPEOF](#flink-sql-typeof-function) ### CAST Casts a value to a different type. Syntax : ```sql CAST(value AS type) ``` Description : The `CAST` function returns the specified value cast to the type specified by `type`.
A cast error throws an exception and fails the job.
Casting binary data to a character string is a special case. In Confluent Cloud, the cast substitutes the Unicode replacement character (`U+FFFD`) for invalid UTF-8 sequences instead of failing, and Confluent Cloud shows a warning that recommends [MAKE_VALID_UTF8](#flink-sql-make-valid-utf8-function). Use `MAKE_VALID_UTF8` to convert binary data explicitly, rather than rely on this implicit substitution. When you want invalid bytes to surface as an error, keep the plain cast. To check whether your data contains invalid sequences, use [IS_VALID_UTF8](#flink-sql-is-valid-utf8-function).
When performing a cast operation that can fail, such as STRING to INT, prefer [TRY_CAST](#flink-sql-try-cast-function), to enable handling errors.
If `table.exec.legacy-cast-behaviour` is enabled, the `CAST` function behaves like `TRY_CAST`. Examples : ```sql -- returns 42 SELECT CAST('42' AS INT);
-- returns NULL of type STRING SELECT CAST(NULL AS STRING);
-- throws an exception and fails the job SELECT CAST('not-a-number' AS INT); ``` ### IS_VALID_UTF8 Checks whether binary data is well-formed UTF-8. Syntax : ```sql IS_VALID_UTF8(bytes) ``` Arguments : `bytes`: a BINARY or VARBINARY value. Returns : A BOOLEAN. If `bytes` is NULL, the return value is NULL. Description : The `IS_VALID_UTF8` function returns TRUE if `bytes` is well-formed UTF-8, and FALSE if it isn’t.
The function treats these sequences as invalid:
- Malformed multi-byte sequences, such as a sequence that’s missing its continuation bytes, or a continuation byte that appears without a lead byte. - *Overlong* encodings that use more bytes than the code point requires. - Code points that exceed the Unicode maximum, `U+10FFFF`. - UTF-16 surrogate values, `U+D800` to `U+DFFF`, which have no UTF-8 representation.
Use this function to separate rows by whether their binary data decodes cleanly. A `WHERE IS_VALID_UTF8(payload)` clause keeps the valid rows. A `WHERE NOT IS_VALID_UTF8(payload)` clause selects the invalid rows, so you can route them to a separate table for inspection.
To convert binary data that might contain invalid sequences, see [MAKE_VALID_UTF8](#flink-sql-make-valid-utf8-function). Examples : ```sql -- returns TRUE SELECT IS_VALID_UTF8(x'48656C6C6F');
-- returns FALSE, because x'80' is a continuation byte with no lead byte SELECT IS_VALID_UTF8(x'80'); ``` ### MAKE_VALID_UTF8 Converts binary data to a string and replaces invalid UTF-8 sequences. Syntax : ```sql MAKE_VALID_UTF8(bytes) ``` Arguments : `bytes`: a BINARY or VARBINARY value. Returns : A STRING. If `bytes` is NULL, the return value is NULL. Description : The `MAKE_VALID_UTF8` function decodes `bytes` as UTF-8 and replaces each invalid sequence with the Unicode replacement character, `U+FFFD`, which most fonts render as a black diamond containing a question mark.
The replacement is lossy and irreversible because you can’t recover the original bytes from the replacement character. To find out whether your data contains invalid sequences before you convert it, use [IS_VALID_UTF8](#flink-sql-is-valid-utf8-function).
`CAST(bytes AS STRING)` performs the same lossy substitution in Confluent Cloud. Use `MAKE_VALID_UTF8` to state it explicitly; for details about the cast’s behavior, see [CAST](#flink-sql-cast-function). Examples : ```sql -- returns "Hello" SELECT MAKE_VALID_UTF8(x'48656C6C6F');
-- returns the U+FFFD replacement character SELECT MAKE_VALID_UTF8(x'80'); ``` ### TRY_CAST Casts a value to a different type and returns NULL on error. Syntax : ```sql TRY_CAST(value AS type) ``` Description : Similar to the [CAST](#flink-sql-cast-function) function, but in case of error, returns NULL rather than failing the job. Examples : ```sql -- returns 42 SELECT TRY_CAST('42' AS INT);
-- returns NULL of type STRING SELECT TRY_CAST(NULL AS STRING);
-- returns NULL of type INT SELECT TRY_CAST('not-a-number' AS INT);
-- returns 0 of type INT SELECT COALESCE(TRY_CAST('not-a-number' AS INT), 0); ``` ### TYPEOF Gets the string representation of a data type. Syntax : ```sql TYPEOF(input) TYPEOF(input, force_serializable) ``` Description : The `TYPEOF` function returns the string representation of the input expression’s data type.
By default, the returned string is a summary string that might omit certain details for readability.
If `force_serializable` is set to TRUE, the string represents a full data type that can be persisted in a catalog.
Anonymous, inline data types have no serializable string representation. In these cases, NULL is returned. Examples : ```sql -- returns "CHAR(13) NOT NULL" SELECT TYPEOF('a string type');
-- returns "INT NOT NULL" SELECT TYPEOF(23);
-- returns "DATE NOT NULL" SELECT TYPEOF(DATE '2023-05-04');
-- returns "NULL" SELECT TYPEOF(NULL); ``` ## Other built-in functions - [Aggregate Functions](aggregate-functions.md#flink-sql-aggregate-functions) - [Changelog Conversion Functions](changelog-conversion.md#flink-sql-changelog-conversion-functions) - [Collection Functions](collection-functions.md#flink-sql-collection-functions) - [Comparison Functions](#flink-sql-comparison-functions) - [Conditional Functions](conditional-functions.md#flink-sql-conditional-functions) - [Datetime Functions](datetime-functions.md#flink-sql-datetime-functions) - [Hash Functions](hash-functions.md#flink-sql-hash-functions) - [JSON Functions](json-functions.md#flink-sql-json-functions) - [ML Preprocessing Functions](ml-preprocessing-functions.md#flink-sql-ml-preprocessing-functions) - [Model Inference Functions](model-inference-functions.md#flink-sql-model-inference-functions) - [Numeric Functions](numeric-functions.md#flink-sql-numeric-functions) - [Search Functions](search-functions.md#flink-sql-search-functions) - [String Functions](string-functions.md#flink-sql-string-functions) - [Table API Functions](table-api-functions.md#flink-table-api-functions) ## Related content - [User-defined Functions](../../concepts/user-defined-functions.md#flink-sql-udfs) - [Create a User Defined Function](../../how-to-guides/create-udf.md#flink-sql-create-udf) #### NOTE This website includes content developed at the [Apache Software Foundation](https://www.apache.org/) under the terms of the [Apache License v2](https://www.apache.org/licenses/LICENSE-2.0.html).