# How to Use Regex in Spark SQL Queries: RLIKE and REGEXP Operators Explained

> Master Spark SQL regex with RLIKE and REGEXP operators. Learn to efficiently filter data using Java regex patterns in your SQL queries.

- Repository: [The Apache Software Foundation/spark](https://github.com/apache/spark)
- Tags: how-to-guide
- Published: 2026-02-11

---

**Spark SQL supports regular expression matching through the `RLIKE` (or `REGEXP`) operator, which compiles Java regex patterns at runtime via the `RLike` expression class.**

When querying string data in Apache Spark, you often need pattern matching beyond simple wildcards. Spark SQL provides full-featured regex capabilities that leverage Java's regular expression engine directly within your queries.

## Understanding Spark SQL Regex Operators

Spark SQL recognizes two equivalent operators for regex matching: **`RLIKE`** and **`REGEXP`**. Both map to the same underlying Catalyst expression and support identical syntax.

### RLIKE vs REGEXP

According to the Spark SQL syntax reference in [`docs/sql-ref-syntax-qry-select-like.md`](https://github.com/apache/spark/blob/main/docs/sql-ref-syntax-qry-select-like.md), these operators are interchangeable aliases. The parser converts both into a `RLike` logical plan node, which is then implemented in [`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/regexpExpressions.scala`](https://github.com/apache/spark/blob/main/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/regexpExpressions.scala).

The `RLike` case class extends `StringRegexExpression` and stores two components: the left-hand side expression (the column to search) and the right-hand side pattern (the Java regular expression).

## How Spark SQL Regex Works

During physical planning, Catalyst generates Java bytecode through the `doGenCode` method that calls `java.util.regex.Pattern`. This evaluation happens row-by-row, returning a Boolean result for each record.

Key implementation details from the source code:

- **Pattern Compilation**: Regex patterns are compiled at runtime using standard Java regex rules
- **No ESCAPE Clause**: Unlike the `LIKE` operator, `RLIKE` does not support the `ESCAPE` keyword
- **Partial Matching**: Returns `true` if the pattern matches any substring; use `^` and `$` anchors to force full-string matching
- **Error Handling**: Invalid patterns throw runtime errors during execution, not at parse time

## Spark SQL Regex Syntax Rules

When writing regex patterns in Spark SQL queries, follow Java regular expression conventions:

- **Case Sensitivity**: Matching is case-sensitive by default
- **Case-Insensitive Matching**: Include the `(?i)` inline flag in your pattern (e.g., `'(?i)abc'`)
- **Backslash Escaping**: Double backslashes are required in SQL string literals (e.g., `'\\d+'` for digit matching)

## Practical Spark SQL Regex Examples

### Basic Pattern Matching

Match email addresses ending with a specific domain:

```sql
SELECT *
FROM   users
WHERE  email RLIKE '.*@example\\.com$';

```

### Using REGEXP Syntax

The `REGEXP` keyword provides identical functionality:

```sql
SELECT *
FROM   logs
WHERE  message REGEXP '\\berror\\b';

```

### Negating Matches

Exclude records that match a pattern using `NOT RLIKE`:

```sql
SELECT *
FROM   events
WHERE  payload NOT RLIKE '^success_.*';

```

### Case-Insensitive Queries

Use the inline `(?i)` flag for case-insensitive matching:

```sql
SELECT *
FROM   products
WHERE  name RLIKE '(?i)apple|orange';

```

### Dynamic Patterns from Columns

Match against a regex stored in another column:

```sql
SELECT *
FROM   patterns
WHERE  text RLIKE pattern_column;

```

### DataFrame API Equivalent

In Scala, use the `rlike` function:

```scala
import org.apache.spark.sql.functions._

val df = spark.read.parquet("hdfs:///data/users.parquet")
val result = df.filter(col("email").rlike(".*@example\\.com$"))
result.show()

```

## Performance Optimization

Spark SQL can push down `RLIKE` predicates to underlying data sources that support regex filtering, including JDBC connections and Parquet files. The optimizer also rewrites `NOT RLIKE` to `!RLIKE` for more efficient execution.

For complex regex patterns, consider the compilation cost. Patterns are compiled per query execution, not per row, but expensive regex operations can become bottlenecks on large datasets.

## Summary

- **RLIKE** and **REGEXP** are interchangeable operators for regex matching in Spark SQL queries
- Patterns follow Java regex syntax and are compiled via `java.util.regex.Pattern` as implemented in [`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/regexpExpressions.scala`](https://github.com/apache/spark/blob/main/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/regexpExpressions.scala)
- Matching is case-sensitive by default; use `(?i)` for insensitive matching
- The `ESCAPE` clause is **not** supported—use standard Java escaping with double backslashes
- Invalid patterns throw runtime errors when the query executes

## Frequently Asked Questions

### What is the difference between LIKE and RLIKE in Spark SQL?

`LIKE` performs simple pattern matching with `%` and `_` wildcards, while `RLIKE` (or `REGEXP`) supports full Java regular expressions. `LIKE` supports an `ESCAPE` clause for customizing escape characters, but `RLIKE` does not.

### How do I make a Spark SQL regex case-insensitive?

Include the `(?i)` inline flag at the beginning of your pattern. For example: `WHERE column RLIKE '(?i)pattern'`. This leverages Java's embedded flag expressions and works within the `RLike` expression implementation.

### Why does my Spark SQL RLIKE query throw a runtime error?

Spark compiles the regex pattern during query execution, not during parsing. If your pattern contains invalid syntax (such as unclosed groups or invalid escape sequences), the `Pattern.compile()` call in the generated code throws an exception when processing the first row.

### Can I use a column value as the regex pattern in RLIKE?

Yes, the right-hand side of `RLIKE` can be a column expression containing a valid regex pattern, not just a string literal. The `RLike` expression class accepts any Catalyst expression that evaluates to a string, allowing dynamic pattern matching against the `text RLIKE pattern_column` syntax.