Predicate Functions ¶

This page lists all predicate functions available in Spark SQL.

!¶

! expr - Logical not.

Arguments:

expr - The boolean expression to negate. An expression that evaluates to a boolean.

Examples:

> SELECT ! true;
 false
> SELECT ! false;
 true
> SELECT ! NULL;
 NULL

Since: 1.0.0

!=¶

expr1 != expr2 - Returns true if expr1 is not equal to expr2, or false otherwise.

Arguments:

expr1, expr2 - the two expressions must be same type or can be casted to a common type, and must be a type that can be used in equality comparison. Map type is not supported. For complex types such array/struct, the data types of fields must be orderable.

Examples:

> SELECT 1 != 2;
 true
> SELECT 1 != '2';
 true
> SELECT true != NULL;
 NULL
> SELECT NULL != NULL;
 NULL

Since: 1.0.0

<¶

expr1 < expr2 - Returns true if expr1 is less than expr2.

Arguments:

expr1, expr2 - the two expressions must be same type or can be casted to a common type, and must be a type that can be ordered. For example, map type is not orderable, so it is not supported. For complex types such array/struct, the data types of fields must be orderable. An expression of any orderable type.

Examples:

> SELECT 1 < 2;
 true
> SELECT 1.1 < '1';
 false
> SELECT to_date('2009-07-30 04:17:52') < to_date('2009-07-30 04:17:52');
 false
> SELECT to_date('2009-07-30 04:17:52') < to_date('2009-08-01 04:17:52');
 true
> SELECT 1 < NULL;
 NULL

Since: 1.0.0

<=¶

expr1 <= expr2 - Returns true if expr1 is less than or equal to expr2.

Arguments:

expr1, expr2 - the two expressions must be same type or can be casted to a common type, and must be a type that can be ordered. For example, map type is not orderable, so it is not supported. For complex types such array/struct, the data types of fields must be orderable. An expression of any orderable type.

Examples:

> SELECT 2 <= 2;
 true
> SELECT 1.0 <= '1';
 true
> SELECT to_date('2009-07-30 04:17:52') <= to_date('2009-07-30 04:17:52');
 true
> SELECT to_date('2009-07-30 04:17:52') <= to_date('2009-08-01 04:17:52');
 true
> SELECT 1 <= NULL;
 NULL

Since: 1.0.0

<=>¶

expr1 <=> expr2 - Returns same result as the EQUAL(=) operator for non-null operands, but returns true if both are null, false if one of the them is null.

Arguments:

expr1, expr2 - the two expressions must be same type or can be casted to a common type, and must be a type that can be used in equality comparison. Map type is not supported. For complex types such array/struct, the data types of fields must be orderable. An expression of any orderable type.

Examples:

> SELECT 2 <=> 2;
 true
> SELECT 1 <=> '1';
 true
> SELECT true <=> NULL;
 false
> SELECT NULL <=> NULL;
 true

Since: 1.1.0

<>¶

expr1 != expr2 - Returns true if expr1 is not equal to expr2, or false otherwise.

Arguments:

expr1, expr2 - the two expressions must be same type or can be casted to a common type, and must be a type that can be used in equality comparison. Map type is not supported. For complex types such array/struct, the data types of fields must be orderable.

Examples:

> SELECT 1 != 2;
 true
> SELECT 1 != '2';
 true
> SELECT true != NULL;
 NULL
> SELECT NULL != NULL;
 NULL

Since: 1.0.0

=¶

expr1 = expr2 - Returns true if expr1 equals expr2, or false otherwise.

Arguments:

expr1, expr2 - the two expressions must be same type or can be casted to a common type, and must be a type that can be used in equality comparison. Map type is not supported. For complex types such array/struct, the data types of fields must be orderable. An expression of any orderable type.

Examples:

> SELECT 2 = 2;
 true
> SELECT 1 = '1';
 true
> SELECT true = NULL;
 NULL
> SELECT NULL = NULL;
 NULL

Since: 1.0.0

==¶

expr1 == expr2 - Returns true if expr1 equals expr2, or false otherwise.

Arguments:

expr1, expr2 - the two expressions must be same type or can be casted to a common type, and must be a type that can be used in equality comparison. Map type is not supported. For complex types such array/struct, the data types of fields must be orderable. An expression of any orderable type.

Examples:

> SELECT 2 == 2;
 true
> SELECT 1 == '1';
 true
> SELECT true == NULL;
 NULL
> SELECT NULL == NULL;
 NULL

Since: 1.0.0

>¶

expr1 > expr2 - Returns true if expr1 is greater than expr2.

Arguments:

expr1, expr2 - the two expressions must be same type or can be casted to a common type, and must be a type that can be ordered. For example, map type is not orderable, so it is not supported. For complex types such array/struct, the data types of fields must be orderable. An expression of any orderable type.

Examples:

> SELECT 2 > 1;
 true
> SELECT 2 > 1.1;
 true
> SELECT to_date('2009-07-30 04:17:52') > to_date('2009-07-30 04:17:52');
 false
> SELECT to_date('2009-07-30 04:17:52') > to_date('2009-08-01 04:17:52');
 false
> SELECT 1 > NULL;
 NULL

Since: 1.0.0

>=¶

expr1 >= expr2 - Returns true if expr1 is greater than or equal to expr2.

Arguments:

expr1, expr2 - the two expressions must be same type or can be casted to a common type, and must be a type that can be ordered. For example, map type is not orderable, so it is not supported. For complex types such array/struct, the data types of fields must be orderable. An expression of any orderable type.

Examples:

> SELECT 2 >= 1;
 true
> SELECT 2.0 >= '2.1';
 false
> SELECT to_date('2009-07-30 04:17:52') >= to_date('2009-07-30 04:17:52');
 true
> SELECT to_date('2009-07-30 04:17:52') >= to_date('2009-08-01 04:17:52');
 false
> SELECT 1 >= NULL;
 NULL

Since: 1.0.0

and ¶

expr1 and expr2 - Logical AND.

Arguments:

expr1 - The first boolean operand. An expression that evaluates to a boolean.
expr2 - The second boolean operand. An expression that evaluates to a boolean.

Examples:

> SELECT true and true;
 true
> SELECT true and false;
 false
> SELECT true and NULL;
 NULL
> SELECT false and NULL;
 false

Since: 1.0.0

equal_null ¶

equal_null(expr1, expr2) - Returns same result as the EQUAL(=) operator for non-null operands, but returns true if both are null, false if one of the them is null.

Arguments:

expr1, expr2 - the two expressions must be same type or can be casted to a common type, and must be a type that can be used in equality comparison. Map type is not supported. For complex types such array/struct, the data types of fields must be orderable. An expression of any orderable type.

Examples:

> SELECT equal_null(3, 3);
 true
> SELECT equal_null(1, '11');
 false
> SELECT equal_null(true, NULL);
 false
> SELECT equal_null(NULL, 'abc');
 false
> SELECT equal_null(NULL, NULL);
 true

Since: 3.4.0

ilike ¶

str ilike pattern[ ESCAPE escape] - Returns true if str matches pattern with escape case-insensitively, null if any arguments are null, false otherwise.

Arguments:

str - a string expression An expression that evaluates to a string.
pattern - a string expression. The pattern is a string which is matched literally and case-insensitively, with exception to the following special symbols:

_ matches any one character in the input (similar to . in posix regular expressions)

% matches zero or more characters in the input (similar to .* in posix regular expressions)

Since Spark 2.0, string literals are unescaped in our SQL parser, see the unescaping rules at String Literal. For example, in order to match "\abc", the pattern should be "\abc".

When SQL config 'spark.sql.parser.escapedStringLiterals' is enabled, it falls back to Spark 1.6 behavior regarding string literal parsing. For example, if the config is enabled, the pattern to match "\abc" should be "\abc".

It's recommended to use a raw string literal (with the r prefix) to avoid escaping special characters in the pattern string if exists. An expression that evaluates to a string.
escape - an character added since Spark 3.0. The default escape character is the '\'. If an escape character precedes a special symbol or another escape character, the following character is matched literally. It is invalid to escape any other character. An expression that evaluates to a string. Must be a constant.

Examples:

> SELECT ilike('Spark', '_Park');
true
> SELECT '\\abc' AS S, S ilike r'\\abc', S ilike '\\\\abc';
\abc    true    true
> SET spark.sql.parser.escapedStringLiterals=true;
spark.sql.parser.escapedStringLiterals  true
> SELECT '%SystemDrive%\Users\John' ilike '\%SystemDrive\%\\users%';
true
> SET spark.sql.parser.escapedStringLiterals=false;
spark.sql.parser.escapedStringLiterals  false
> SELECT '%SystemDrive%\\USERS\\John' ilike r'%SystemDrive%\\Users%';
true
> SELECT '%SystemDrive%/Users/John' ilike '/%SYSTEMDrive/%//Users%' ESCAPE '/';
true

Note:

Use RLIKE to match with standard regular expressions.

Since: 3.3.0

in ¶

expr1 in(expr2, expr3, ...) - Returns true if expr equals to any valN.

Arguments:

expr1, expr2, expr3, ... - the arguments must be same type.

Examples:

> SELECT 1 in(1, 2, 3);
 true
> SELECT 1 in(2, 3, 4);
 false
> SELECT named_struct('a', 1, 'b', 2) in(named_struct('a', 1, 'b', 1), named_struct('a', 1, 'b', 3));
 false
> SELECT named_struct('a', 1, 'b', 2) in(named_struct('a', 1, 'b', 2), named_struct('a', 1, 'b', 3));
 true

Since: 1.0.0

isnan ¶

isnan(expr) - Returns true if expr is NaN, or false otherwise.

Arguments:

expr - The expression to test for NaN. An expression that evaluates to a double or float.

Examples:

> SELECT isnan(cast('NaN' as double));
 true

Since: 1.5.0

isnotnull ¶

isnotnull(expr) - Returns true if expr is not null, or false otherwise.

Examples:

> SELECT isnotnull(1);
 true

Since: 1.0.0

isnull ¶

isnull(expr) - Returns true if expr is null, or false otherwise.

Examples:

> SELECT isnull(1);
 false

Since: 1.0.0

like ¶

str like pattern[ ESCAPE escape] - Returns true if str matches pattern with escape, null if any arguments are null, false otherwise.

Arguments:

str - a string expression An expression that evaluates to a string.
pattern - a string expression. The pattern is a string which is matched literally, with exception to the following special symbols:

_ matches any one character in the input (similar to . in posix regular expressions)\ % matches zero or more characters in the input (similar to .* in posix regular expressions)

Since Spark 2.0, string literals are unescaped in our SQL parser, see the unescaping rules at String Literal. For example, in order to match "\abc", the pattern should be "\abc".

When SQL config 'spark.sql.parser.escapedStringLiterals' is enabled, it falls back to Spark 1.6 behavior regarding string literal parsing. For example, if the config is enabled, the pattern to match "\abc" should be "\abc".

It's recommended to use a raw string literal (with the r prefix) to avoid escaping special characters in the pattern string if exists. An expression that evaluates to a string.
escape - an character added since Spark 3.0. The default escape character is the '\'. If an escape character precedes a special symbol or another escape character, the following character is matched literally. It is invalid to escape any other character. An expression that evaluates to a string. Must be a constant.

Examples:

> SELECT like('Spark', '_park');
true
> SELECT '\\abc' AS S, S like r'\\abc', S like '\\\\abc';
\abc    true    true
> SET spark.sql.parser.escapedStringLiterals=true;
spark.sql.parser.escapedStringLiterals  true
> SELECT '%SystemDrive%\Users\John' like '\%SystemDrive\%\\Users%';
true
> SET spark.sql.parser.escapedStringLiterals=false;
spark.sql.parser.escapedStringLiterals  false
> SELECT '%SystemDrive%\\Users\\John' like r'%SystemDrive%\\Users%';
true
> SELECT '%SystemDrive%/Users/John' like '/%SystemDrive/%//Users%' ESCAPE '/';
true

Note:

Use RLIKE to match with standard regular expressions.

Since: 1.0.0

not ¶

not expr - Logical not.

Arguments:

expr - The boolean expression to negate. An expression that evaluates to a boolean.

Examples:

> SELECT not true;
 false
> SELECT not false;
 true
> SELECT not NULL;
 NULL

Since: 1.0.0

or ¶

expr1 or expr2 - Logical OR.

Arguments:

expr1 - The left boolean operand of the logical OR. An expression that evaluates to a boolean.
expr2 - The right boolean operand of the logical OR. An expression that evaluates to a boolean.

Examples:

> SELECT true or false;
 true
> SELECT false or false;
 false
> SELECT true or NULL;
 true
> SELECT false or NULL;
 NULL

Since: 1.0.0

regexp ¶

regexp(str, regexp) - Returns true if str matches regexp, or false otherwise.

Arguments:

str - a string expression An expression that evaluates to a string.
regexp - a string expression. The regex string should be a Java regular expression. An expression that evaluates to a string.

Since Spark 2.0, string literals (including regex patterns) are unescaped in our SQL parser, see the unescaping rules at String Literal. For example, to match "\abc", a regular expression for regexp can be "^\abc$".

There is a SQL config 'spark.sql.parser.escapedStringLiterals' that can be used to fallback to the Spark 1.6 behavior regarding string literal parsing. For example, if the config is enabled, the regexp that can match "\abc" is "^\abc$".

It's recommended to use a raw string literal (with the r prefix) to avoid escaping special characters in the pattern string if exists.

Examples:

> SET spark.sql.parser.escapedStringLiterals=true;
spark.sql.parser.escapedStringLiterals  true
> SELECT regexp('%SystemDrive%\Users\John', '%SystemDrive%\\Users.*');
true
> SET spark.sql.parser.escapedStringLiterals=false;
spark.sql.parser.escapedStringLiterals  false
> SELECT regexp('%SystemDrive%\\Users\\John', '%SystemDrive%\\\\Users.*');
true
> SELECT regexp('%SystemDrive%\\Users\\John', r'%SystemDrive%\\Users.*');
true

Note:

Use LIKE to match with simple string pattern.

Since: 3.2.0

regexp_like ¶

regexp_like(str, regexp) - Returns true if str matches regexp, or false otherwise.

Arguments:

str - a string expression An expression that evaluates to a string.
regexp - a string expression. The regex string should be a Java regular expression. An expression that evaluates to a string.

Since Spark 2.0, string literals (including regex patterns) are unescaped in our SQL parser, see the unescaping rules at String Literal. For example, to match "\abc", a regular expression for regexp can be "^\abc$".

There is a SQL config 'spark.sql.parser.escapedStringLiterals' that can be used to fallback to the Spark 1.6 behavior regarding string literal parsing. For example, if the config is enabled, the regexp that can match "\abc" is "^\abc$".

It's recommended to use a raw string literal (with the r prefix) to avoid escaping special characters in the pattern string if exists.

Examples:

> SET spark.sql.parser.escapedStringLiterals=true;
spark.sql.parser.escapedStringLiterals  true
> SELECT regexp_like('%SystemDrive%\Users\John', '%SystemDrive%\\Users.*');
true
> SET spark.sql.parser.escapedStringLiterals=false;
spark.sql.parser.escapedStringLiterals  false
> SELECT regexp_like('%SystemDrive%\\Users\\John', '%SystemDrive%\\\\Users.*');
true
> SELECT regexp_like('%SystemDrive%\\Users\\John', r'%SystemDrive%\\Users.*');
true

Note:

Use LIKE to match with simple string pattern.

Since: 3.2.0

rlike ¶

rlike(str, regexp) - Returns true if str matches regexp, or false otherwise.

Arguments:

str - a string expression An expression that evaluates to a string.
regexp - a string expression. The regex string should be a Java regular expression. An expression that evaluates to a string.

Since Spark 2.0, string literals (including regex patterns) are unescaped in our SQL parser, see the unescaping rules at String Literal. For example, to match "\abc", a regular expression for regexp can be "^\abc$".

There is a SQL config 'spark.sql.parser.escapedStringLiterals' that can be used to fallback to the Spark 1.6 behavior regarding string literal parsing. For example, if the config is enabled, the regexp that can match "\abc" is "^\abc$".

It's recommended to use a raw string literal (with the r prefix) to avoid escaping special characters in the pattern string if exists.

Examples:

> SET spark.sql.parser.escapedStringLiterals=true;
spark.sql.parser.escapedStringLiterals  true
> SELECT rlike('%SystemDrive%\Users\John', '%SystemDrive%\\Users.*');
true
> SET spark.sql.parser.escapedStringLiterals=false;
spark.sql.parser.escapedStringLiterals  false
> SELECT rlike('%SystemDrive%\\Users\\John', '%SystemDrive%\\\\Users.*');
true
> SELECT rlike('%SystemDrive%\\Users\\John', r'%SystemDrive%\\Users.*');
true

Note:

Use LIKE to match with simple string pattern.

Since: 1.0.0