Predicate Functions

This page lists all predicate functions available in Spark SQL.

!

! expr - Logical not.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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

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.

Examples:

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

Since: 1.0.0


or

expr1 or expr2 - Logical OR.

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

  • regexp - a string expression. The regex string should be a Java regular expression.

    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

  • regexp - a string expression. The regex string should be a Java regular expression.

    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

  • regexp - a string expression. The regex string should be a Java regular expression.

    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