> ## Documentation Index
> Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Conditional and predicate functions

> Spark conditional and predicate functions supported in SparkSQL and Databricks compatibility modes.

Preview

Spark [conditional](https://spark.apache.org/docs/latest/sql-ref-functions-builtin.html#conditional-functions) and [predicate](https://spark.apache.org/docs/latest/sql-ref-functions-builtin.html#predicate-functions) functions in [SparkSQL and Databricks compatibility modes](https://docs.firebolt.io/reference-sql/spark/overview.md). Both modes share these functions. Support is one of: Supported, With limitations, or Not supported. Supported means every example in the Spark reference returns what Spark returns; a note under With limitations quotes a call that differs.

## Conditional functions

| Function | Support | Notes |
| :- | :- | :- |
| [`BETWEEN`](https://spark.apache.org/docs/4.1.0/api/sql/#between) | Supported | |
| [`COALESCE`](https://spark.apache.org/docs/4.1.0/api/sql/#coalesce) | Supported | |
| [`IF`](https://spark.apache.org/docs/4.1.0/api/sql/#if) | Supported | |
| [`IFNULL`](https://spark.apache.org/docs/4.1.0/api/sql/#ifnull) | Supported | |
| [`NULLIF`](https://spark.apache.org/docs/4.1.0/api/sql/#nullif) | Supported | |
| [`NULLIFZERO`](https://spark.apache.org/docs/4.1.0/api/sql/#nullifzero) | Supported | |
| [`NVL`](https://spark.apache.org/docs/4.1.0/api/sql/#nvl) | Supported | |
| [`NVL2`](https://spark.apache.org/docs/4.1.0/api/sql/#nvl2) | Supported | |
| [`WHEN`](https://spark.apache.org/docs/4.1.0/api/sql/#when) | With limitations | Returns `DOUBLE`, where Spark returns `NUMERIC(11, 1)`. |
| [`ZEROIFNULL`](https://spark.apache.org/docs/4.1.0/api/sql/#zeroifnull) | With limitations | Returns `DOUBLE`, where Spark returns `INTEGER`. |
| `NANVL` | Not supported | |

## Predicate functions

| Function | Support | Notes |
| :- | :- | :- |
| [`AND`](https://spark.apache.org/docs/4.1.0/api/sql/#and) | Supported | |
| [`EQUAL_NULL`](https://spark.apache.org/docs/4.1.0/api/sql/#equal_null) | Supported | |
| [`ISNOTNULL`](https://spark.apache.org/docs/4.1.0/api/sql/#isnotnull) | Supported | |
| [`ISNULL`](https://spark.apache.org/docs/4.1.0/api/sql/#isnull) | Supported | |
| [`NOT`](https://spark.apache.org/docs/4.1.0/api/sql/#not) | Supported | |
| [`OR`](https://spark.apache.org/docs/4.1.0/api/sql/#or) | Supported | |
| [`REGEXP`](https://spark.apache.org/docs/4.1.0/api/sql/#regexp) | Supported | |
| [`REGEXP_LIKE`](https://spark.apache.org/docs/4.1.0/api/sql/#regexp_like) | Supported | |
| [`RLIKE`](https://spark.apache.org/docs/4.1.0/api/sql/#rlike) | Supported | |
| [`ILIKE`](https://spark.apache.org/docs/4.1.0/api/sql/#ilike) | With limitations | `'%SystemDrive%/Users/John' ilike '/%SYSTEMDrive/%//Users%' ESCAPE '/'` is not supported. |
| [`IN`](https://spark.apache.org/docs/4.1.0/api/sql/#in) | With limitations | Not supported for `STRUCT` values. |
| [`LIKE`](https://spark.apache.org/docs/4.1.0/api/sql/#like) | With limitations | `'%SystemDrive%/Users/John' like '/%SystemDrive/%//Users%' ESCAPE '/'` is not supported. |
| `ISNAN` | Not supported | |


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.