Good engineers know 3 min read · Strings 3 practice problems ↓

regexp_extract

Pull fields out of text with a regular expression, and what it returns when nothing matches.

On this page

Show code in

Every code block on the page follows this.

You will learn

How regexp_extract pulls a capture group out of text
What it returns when the pattern does not match
How to escape patterns in Python and SQL
When to use regexp_replace, rlike or split instead

Read first

select · 4 min read

Comfortable with these? Read on.

TL;DR F.regexp_extract(col, pattern, idx) returns capture group idx of the first match. If the pattern does not match, it returns an empty string, not null.

What it does

Spark uses Java regular expressions. idx=0 returns the whole match; 1, 2, ... return the parenthesised groups.

Step by step

Pattern (\d{4}-\d{2}-\d{2}) (\w+) \[(\w+)\] on 2025-03-01 ERROR [db] timeout:

Group	Pattern part	Matches
1	(\d{4}-\d{2}-\d{2})	2025-03-01
2	(\w+)	ERROR
3	\[(\w+)\]	db

Run the example

PySparkSpark SQL

from pyspark.sql import functions as F
pattern = r"(\d{4}-\d{2}-\d{2}) (\w+) \[(\w+)\]"
result = logs.select(
    F.regexp_extract("raw", pattern, 1).alias("day"),
    F.regexp_extract("raw", pattern, 2).alias("level"),
    F.regexp_extract("raw", pattern, 3).alias("component"),
)

SELECT regexp_extract(raw, '(\\d{4}-\\d{2}-\\d{2}) (\\w+) \\[(\\w+)\\]', 1) AS day,
       regexp_extract(raw, '(\\d{4}-\\d{2}-\\d{2}) (\\w+) \\[(\\w+)\\]', 2) AS level,
       regexp_extract(raw, '(\\d{4}-\\d{2}-\\d{2}) (\\w+) \\[(\\w+)\\]', 3) AS component
FROM logs

Switch to PySpark to edit and run this example in your browser.

The corrupted line produces three empty strings. Treat them as missing with F.nullif(F.regexp_extract(...), F.lit("")), or filter with rlike first.

Escaping

In Python, use raw strings, r"\d+", so Python does not consume the backslashes.
In Spark SQL string literals, a backslash must be doubled: '\\d+'. Setting spark.sql.parser.escapedStringLiterals=true turns that off.
Inside a pattern, escape metacharacters you mean literally: \., \[, \|.

The regex family

Function	Use
`rlike(pattern)`	Filter: does the string match?
`regexp_extract`	Pull one group out
`regexp_extract_all`	All matches of a group, as an array (Spark 3.1+)
`regexp_replace`	Replace every match: `regexp_replace("phone", r"\D", "")` keeps only digits
`split`	Split on a regex into an array

Performance

Regex is evaluated row by row on the JVM and is far faster than a Python UDF, which ships every row to a Python process. Still, regex on billions of rows costs real CPU: extract once into columns and store them, rather than re-parsing raw text in every query. Avoid patterns with nested quantifiers like (a+)+, which can backtrack exponentially on some inputs.

Common mistakes

Expecting null on no match

regexp_extract returns an empty string. Convert with nullif if you need null.

Single backslashes in SQL

'\d' in SQL becomes d. Double them.

Using a Python UDF for parsing

Much slower than built-in regex functions.

Key takeaways

regexp_extract returns a capture group of the first match.
No match gives an empty string, not null.
Use raw strings in Python and double backslashes in SQL.
Built-in regex is much faster than a Python UDF.

Check yourself

3 questions

1. What does regexp_extract return when the pattern does not match?

Show the answer

An empty string. It returns an empty string. Use nullif(..., "") to get null.

2. What does group index 0 return?

Show the answer

The whole match. Index 0 is the entire match; groups start at 1.

3. How do you write the regex \d+ inside a Spark SQL string literal by default?

Show the answer

'\\d+'. SQL string literals consume one level of backslashes, so they must be doubled.

Practice it

Interview problems that use regexp_extract: write the PySpark, run it, and get graded on hidden tests.

Solve: Parse UTM Parameters →

Go deeper

PySpark functions
select PySpark functions
filter / where PySpark functions
explode

Primary sources: functions.regexp_extract · Java Pattern syntax

← Previous
trunc / add_months Next →
rangeBetween