PySpark SQL split() Function: Usage & Examples

Formulas & functions · Updated November 22, 2026

The split() function in PySpark SQL splits a string column into an array of substrings based on a delimiter, returning an ArrayType column. You use it as pyspark.sql.functions.split(str, pattern, limit=-1), where pattern is a regular expression string and limit optionally controls how many splits are performed.

Background: Why split() Exists in PySpark

PySpark is the Python API for Apache Spark, a distributed computing engine designed for large-scale data processing. In Spark SQL, string manipulation is a core need—raw data often arrives as delimited text (CSV, log files, JSON strings) that must be broken into structured columns for analysis. The split() function is part of the functions module, which provides built-in transformations for DataFrame columns. Unlike Python’s native str.split(), PySpark’s version operates on entire columns in parallel across a cluster, and it uses Java regular expressions (the same syntax as Spark’s underlying Scala API). This makes it powerful but also introduces quirks—for example, a literal dot . must be escaped as \. because it matches any character in regex.

How split() Works: Syntax and Return Type

The function signature is:

pyspark.sql.functions.split(str, pattern, limit=-1)
  • str: a Column or string expression containing the text to split.
  • pattern: a string representing a Java regular expression. For a literal delimiter, escape special characters.
  • limit: an integer controlling the number of splits. Default is -1, meaning all possible splits. A positive number n returns at most n substrings (the last substring contains the remainder). A zero or negative value (other than -1) behaves like -1.

The return type is ArrayType(StringType). For example, given a DataFrame with a column text containing "apple,banana,cherry", the code:

from pyspark.sql.functions import split
df.withColumn("fruits", split(df.text, ","))

produces a new column fruits with value ["apple", "banana", "cherry"].

Using limit to Control Splits

The limit parameter is often misunderstood. With limit=2, the string "a,b,c,d" splits into ["a", "b,c,d"]—the first delimiter is used, and everything after is kept as one element. This is useful when you only need the first few fields and want to preserve the rest. For instance, parsing a timestamp like "2024-05-01 10:30:00" with split(col, " ", 2) gives ["2024-05-01", "10:30:00"]. If you omit limit, the default -1 splits on every match, so "a,b,c" becomes three elements.

Common Patterns and Regex Pitfalls

Because pattern is a regular expression, literal characters like ., |, *, +, ?, (, ), [, ], {, }, ^, $, and \ need escaping. For example, to split a URL by dots, use split(url, "\\.") (two backslashes in Python string). To split by a pipe character, use split(col, "\\|"). If you want to split on multiple possible delimiters, use a character class: split(col, "[,;]") splits on comma or semicolon. A common mistake is using split(col, ".") expecting a literal dot—this actually matches any character, producing an array of empty strings (except for the last character). Always test your regex on a small sample.

Accessing Split Results and Handling Nulls

After splitting, you often want individual elements. Use indexing with getItem() or the bracket notation. For example:

from pyspark.sql.functions import split
df.withColumn("first", split(df.text, ",").getItem(0))
df.withColumn("second", split(df.text, ",")[1])

Both extract the first and second elements. If the array is shorter than the index, the result is null. If the input column is null, split() returns null (not an array). To avoid nulls, use coalesce() or filter before splitting. For example, df.filter(df.text.isNotNull()).

Comparing split() with Other PySpark String Functions

PySpark offers several related functions:

  • regexp_extract(): extracts a specific group from a regex match, returning a string, not an array.
  • substring(): extracts a fixed-length substring by position.
  • split() vs explode(): split() creates an array column; explode() turns that array into multiple rows. They are often used together: df.withColumn("arr", split(df.text, ",")).select(explode("arr")).
  • regexp_replace(): replaces regex matches, useful when you want to change delimiters before splitting.

Choosing between them depends on whether you need a single value or a collection. If you need the second word in a sentence, split() plus getItem(1) is straightforward. If you need all words as separate rows for aggregation, use split() with explode().

Performance and Best Practices

In distributed processing, split() is a deterministic, per-row operation that parallelizes well. However, regex compilation can be expensive if the pattern is complex and the dataset is huge. Spark caches compiled patterns, so reuse the same pattern across rows. Avoid splitting on extremely long strings with many delimiters if you only need the first few elements—use a positive limit to reduce memory. Also, be mindful that the resulting array column increases data size; if you only need a few elements, extract them immediately and drop the array column. For very large datasets, consider using substring_index() (available in Spark 3.0+) which returns a string directly for the nth occurrence of a delimiter, avoiding array creation.

Frequently Asked Questions

Q: How do I split a column into multiple columns?
After splitting, you can use getItem() to create separate columns, or use select() with multiple getItem() calls. For example: df.select(split(df.text, ",").getItem(0).alias("col1"), split(df.text, ",").getItem(1).alias("col2")). If you need a variable number of columns, consider using posexplode() to pivot rows into columns, but that is more complex.

Q: What is the difference between split() and regexp_extract()?
split() returns an array of all substrings separated by the regex pattern. regexp_extract() returns a single string matching a specific regex group. Use split() when you want all parts; use regexp_extract() when you want one specific piece, like a date from a log line.

Q: Why does split() return an empty array for a string with no delimiter?
If the delimiter is not found, split() returns an array containing the original string as its only element—not an empty array. For example, split("hello", ",") returns ["hello"]. If you see an empty array, check that your input is null or that your regex matches an empty string (which is possible with patterns like "").

Q: Can I split on a literal string that contains regex special characters?
Yes, but you must escape each special character. Use Pattern.quote() in Java, but in PySpark you can manually escape. For example, to split on the literal "a.b", use split(col, "a\\.b"). Alternatively, use split(col, "\\Qa.b\\E") to quote the literal—this works in Java regex.

Q: Does split() work on binary or numeric columns?
No, split() expects a string column. If you have a numeric column, cast it to string first: split(col("num").cast("string"), ","). For binary data, you must decode to string first using decode().

Q: How do I split a string and then count the number of elements?
Use size() on the array: df.withColumn("count", size(split(df.text, ","))). This returns the number of substrings. For example, "a,b,c" gives 3.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.