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

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

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:

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

Using REGEXP Syntax

The REGEXP keyword provides identical functionality:

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

Negating Matches

Exclude records that match a pattern using NOT RLIKE:

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

Case-Insensitive Queries

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

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

Dynamic Patterns from Columns

Match against a regex stored in another column:

SELECT *
FROM   patterns
WHERE  text RLIKE pattern_column;

DataFrame API Equivalent

In Scala, use the rlike function:

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

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}