Conditional, comparison and conversion functions

Every Spark SQL conditional, comparison and conversion function on Databasin Sail: whether it is supported, how it differs from Apache Spark, and examples with results.

Last updated October 20, 2026

Reading time 15 min read

This page lists every Spark SQL conditional, comparison and conversion function: 46 work on Databasin Sail as in Apache Spark and 3 work with a difference noted below. Each function shows its status on Sail, its usage, and examples whose results on Sail match Spark's.

Every supported function also works from PySpark through pyspark.sql.functions when PySpark has a wrapper for it. The Sail SQL reference lists every function page.

Conditional

between · case · coalesce · if · ifnull · nanvl · nullif · nullifzero · nvl · nvl2 · when · zeroifnull

between

Supported.

input [NOT] between lower AND upper: evaluate if input is [not] in between lower and upper

input: An expression that is being compared with lower and upper bound.
lower: Lower bound of the between check.
upper: Upper bound of the between check.

Since Spark 1.0.0.

SELECT 0.5 between 0.1 AND 1.0;
-- true

case

Supported.

CASE expr1 WHEN expr2 THEN expr3 [WHEN expr4 THEN expr5]* [ELSE expr6] END: When expr1 = expr2, returns expr3; when expr1 = expr4, return expr5; else return expr6.

coalesce

Supported.

coalesce(expr1, expr2, ...): Returns the first non-null argument if exists. Otherwise, null.

Since Spark 1.0.0.

SELECT coalesce(NULL, 1, NULL);
-- 1

if

Supported.

if(expr1, expr2, expr3): If expr1 evaluates to true, then returns expr2; otherwise returns expr3.

Since Spark 1.0.0.

SELECT if(1 < 2, 'a', 'b');
-- a

ifnull

Supported.

ifnull(expr1, expr2): Returns expr2 if expr1 is null, or expr1 otherwise.

Since Spark 2.0.0.

SELECT ifnull(NULL, array('2'));
-- ["2"]

nanvl

Supported.

nanvl(expr1, expr2): Returns expr1 if it's not NaN, or expr2 otherwise.

Since Spark 1.5.0.

SELECT nanvl(cast('NaN' as double), 123);
-- 123.0

nullif

Supported.

nullif(expr1, expr2): Returns null if expr1 equals to expr2, or expr1 otherwise.

Since Spark 2.0.0.

SELECT nullif(2, 2);
-- NULL

nullifzero

Supported.

nullifzero(expr): Returns null if expr is equal to zero, or expr otherwise.

Since Spark 4.0.0.

SELECT nullifzero(0);
-- NULL

SELECT nullifzero(2);
-- 2

nvl

Supported.

nvl(expr1, expr2): Returns expr2 if expr1 is null, or expr1 otherwise.

Since Spark 2.0.0.

SELECT nvl(NULL, array('2'));
-- ["2"]

nvl2

Supported.

nvl2(expr1, expr2, expr3): Returns expr2 if expr1 is not null, or expr3 otherwise.

Since Spark 2.0.0.

SELECT nvl2(NULL, 2, 1);
-- 1

when

Supported.

CASE WHEN expr1 THEN expr2 [WHEN expr3 THEN expr4]* [ELSE expr5] END: When expr1 = true, returns expr2; else when expr3 = true, returns expr4; else returns expr5.

expr1, expr3: the branch condition expressions should all be boolean type.
expr2, expr4, expr5: the branch value expressions and else value expression should all be same type or coercible to a common type.

Since Spark 1.0.1.

SELECT CASE WHEN 1 > 0 THEN 1 WHEN 2 > 0 THEN 2.0 ELSE 1.2 END;
-- 1.0

SELECT CASE WHEN 1 < 0 THEN 1 WHEN 2 > 0 THEN 2.0 ELSE 1.2 END;
-- 2.0

SELECT CASE WHEN 1 < 0 THEN 1 WHEN 2 < 0 THEN 2.0 END;
-- NULL

zeroifnull

Supported.

zeroifnull(expr): Returns zero if expr is equal to null, or expr otherwise.

Since Spark 4.0.0.

SELECT zeroifnull(NULL);
-- 0

SELECT zeroifnull(2);
-- 2

Predicate

! · != · < · <= · <=> · <> · = · == · > · >= · and · equal_null · ilike · in · isnan · isnotnull · isnull · like · not · or · regexp · regexp_like · rlike

`!`

Supported.

! expr: Logical not.

Since Spark 1.0.0.

SELECT ! true;
-- false

SELECT ! false;
-- true

SELECT ! NULL;
-- NULL

`!=`

Supported.

expr1 != expr2: Returns true if expr1 is not equal to expr2.

`<`

Supported.

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

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.

Since Spark 1.0.0.

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

`<=`

Supported.

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

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.

Since Spark 1.0.0.

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

`<=>`

Supported.

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.

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.

Since Spark 1.1.0.

SELECT 2 <=> 2;
-- true

SELECT 1 <=> '1';
-- true

SELECT true <=> NULL;
-- false

SELECT NULL <=> NULL;
-- true

`<>`

Supported.

expr1 <> expr2: Returns true if expr1 is not equal to expr2.

`=`

Supported.

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

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.

Since Spark 1.0.0.

SELECT 2 = 2;
-- true

SELECT 1 = '1';
-- true

SELECT true = NULL;
-- NULL

SELECT NULL = NULL;
-- NULL

`==`

Supported.

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

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.

Since Spark 1.0.0.

SELECT 2 == 2;
-- true

SELECT 1 == '1';
-- true

SELECT true == NULL;
-- NULL

SELECT NULL == NULL;
-- NULL

`>`

Supported. Results match Spark; the auto-generated result column name can differ in some examples.

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

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.

Since Spark 1.0.0.

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

`>=`

Supported.

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

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.

Since Spark 1.0.0.

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

and

Supported.

expr1 and expr2: Logical AND.

Since Spark 1.0.0.

SELECT true and true;
-- true

SELECT true and false;
-- false

SELECT true and NULL;
-- NULL

SELECT false and NULL;
-- false

equal_null

Supported.

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.

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.

Since Spark 3.4.0.

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

ilike

Supported. Results match. Note: spark.sql.parser.escapedStringLiterals=true is not honored (backslashes in literals stay escapes).

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

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.

Note from Spark: Use RLIKE to match with standard regular expressions.

Since Spark 3.3.0.

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

in

Supported.

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

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

Since Spark 1.0.0.

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

isnan

Supported.

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

Since Spark 1.5.0.

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

isnotnull

Supported.

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

Since Spark 1.0.0.

SELECT isnotnull(1);
-- true

isnull

Supported.

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

Since Spark 1.0.0.

SELECT isnull(1);
-- false

like

Supported. Results match. Note: spark.sql.parser.escapedStringLiterals=true is not honored (backslashes in literals stay escapes).

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

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.

Note from Spark: Use RLIKE to match with standard regular expressions.

Since Spark 1.0.0.

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

not

Supported.

not expr: Logical not.

Since Spark 1.0.0.

SELECT not true;
-- false

SELECT not false;
-- true

SELECT not NULL;
-- NULL

or

Supported.

expr1 or expr2: Logical OR.

Since Spark 1.0.0.

SELECT true or false;
-- true

SELECT false or false;
-- false

SELECT true or NULL;
-- true

SELECT false or NULL;
-- NULL

regexp

Supported, differs from Spark. With spark.sql.parser.escapedStringLiterals=true, Sail still treats backslashes in string literals as escapes, so the Spark example with a raw Windows path fails.

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

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.

Note from Spark: Use LIKE to match with simple string pattern.

Since Spark 3.2.0.

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

regexp_like

Supported, differs from Spark. With spark.sql.parser.escapedStringLiterals=true, Sail still treats backslashes in string literals as escapes, so the Spark example with a raw Windows path fails.

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

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.

Note from Spark: Use LIKE to match with simple string pattern.

Since Spark 3.2.0.

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

rlike

Supported, differs from Spark. With spark.sql.parser.escapedStringLiterals=true, Sail still treats backslashes in string literals as escapes, so the Spark example with a raw Windows path fails.

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

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.

Note from Spark: Use LIKE to match with simple string pattern.

Since Spark 1.0.0.

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

Conversion

bigint · binary · boolean · cast · date · decimal · double · float · int · smallint · string · time · timestamp · tinyint

bigint

Supported.

bigint(expr): Casts the value expr to the target data type bigint.

Since Spark 2.0.1.

binary

Supported.

binary(expr): Casts the value expr to the target data type binary.

Since Spark 2.0.1.

boolean

Supported.

boolean(expr): Casts the value expr to the target data type boolean.

Since Spark 2.0.1.

cast

Supported.

cast(expr AS type): Casts the value expr to the target data type type. expr :: type alternative casting syntax is also supported.

Since Spark 1.0.0.

SELECT cast('10' as int);
-- 10

SELECT '10' :: int;
-- 10

date

Supported.

date(expr): Casts the value expr to the target data type date.

Since Spark 2.0.1.

decimal

Supported.

decimal(expr): Casts the value expr to the target data type decimal.

Since Spark 2.0.1.

double

Supported.

double(expr): Casts the value expr to the target data type double.

Since Spark 2.0.1.

float

Supported.

float(expr): Casts the value expr to the target data type float.

Since Spark 2.0.1.

int

Supported.

int(expr): Casts the value expr to the target data type int.

Since Spark 2.0.1.

smallint

Supported.

smallint(expr): Casts the value expr to the target data type smallint.

Since Spark 2.0.1.

string

Supported.

string(expr): Casts the value expr to the target data type string.

Since Spark 2.0.1.

time

Supported. New in Spark 2.0.1. Matches Spark 4.2.0 run with spark.sql.timeType.enabled=true (TIME is off by default in Spark 4.2; on in Sail).

time(expr): Casts the value expr to the target data type time.

timestamp

Supported.

timestamp(expr): Casts the value expr to the target data type timestamp.

Since Spark 2.0.1.

tinyint

Supported.

tinyint(expr): Casts the value expr to the target data type tinyint.

Since Spark 2.0.1.

Function names, usage text, argument descriptions and examples are from the Apache Spark SQL built-in functions documentation, Copyright The Apache Software Foundation, licensed under the Apache License, Version 2.0. Apache Spark is a trademark of the Apache Software Foundation. Sail status and notes are from Databasin.

Where to go next

Sail SQL referenceStatements, and every function page. Spark compatibilityWhere Sail differs from Apache Spark. Aggregate functionsMore functions. Window, generator and table functionsMore functions.