The split() function in Databricks SQL and PySpark splits a string into an array of substrings based on a delimiter. To use it in SQL, call split(string, delimiter); in PySpark, use pyspark.sql.functions.split().
What Is the split() Function in Databricks?
Databricks is a cloud-based platform built on Apache Spark, and it offers two main interfaces: Spark SQL (also called Databricks SQL) and PySpark (the Python API). The split() function exists in both, with nearly identical behavior. It takes a string column or literal and a delimiter pattern, then returns an array of strings. The delimiter is treated as a regular expression, not just a literal character, which gives you flexibility but also requires care with special characters.
How split() Works in Databricks SQL
In Databricks SQL, the syntax is:
split(str, delimiter)
For example, to split a comma-separated list:
SELECT split('apple,banana,cherry', ',') AS fruits;
This returns an array: ["apple", "banana", "cherry"]. The delimiter is a regex, so if you want to split on a literal period, you must escape it: split('a.b.c', '\.'). If the delimiter appears at the start or end, or if there are consecutive delimiters, empty strings are included in the result. For instance, split('a,,b', ',') returns ["a", "", "b"].
How split() Works in PySpark
In PySpark, you use the split() function from pyspark.sql.functions. Here’s a simple example:
from pyspark.sql import SparkSession
from pyspark.sql.functions import split
spark = SparkSession.builder.getOrCreate()
df = spark.createDataFrame([("John Doe",)], ["name"])
df.select(split(df.name, " ").alias("name_parts")).show()
This splits the name on spaces, producing an array like ["John", "Doe"]. You can then use array functions like getItem() or explode() to work with the elements.
Common Use Cases and Examples
Here are typical scenarios where you’d use split():
- Parsing CSV data stored in a single column (though it’s better to use the CSV reader for files).
- Extracting a specific part of a string, like getting the domain from an email:
split(email, '@')[1]. - Handling tags or keywords stored as comma-separated strings.
- Normalizing data by splitting and then exploding into multiple rows.
For example, in SQL, to get the second element of a split:
SELECT split('a,b,c', ',')[1] AS second; -- returns 'b'
In PySpark, you can do df.select(split(df.col, ',').getItem(1)).
Choosing the Right Delimiter and Handling Edge Cases
Because the delimiter is a regex, you need to escape special characters like |, ., *, +, ?, and parentheses. For a pipe-delimited string, use split('a|b', '\|'). If you want to split on multiple different delimiters, you can use a regex character class like [,;] to split on either comma or semicolon.
Edge cases to consider:
- Empty string:
split('', ',')returns an array with one empty string. - Null values:
split(NULL, ',')returns NULL. - Trailing delimiters:
split('a,b,', ',')returns["a", "b", ""].
If you need to ignore empty strings, you can filter them out after splitting, or use a regex that handles them, like split(str, ',+') to treat consecutive commas as one delimiter.
split() vs. Other String Functions in Databricks
Databricks offers several related functions. split() is the main one for converting a string to an array. Alternatives include:
regexp_extract()– extracts a specific substring using a regex pattern.substring()orsubstr()– extracts a fixed-length substring by position.explode()– a table-generating function that turns an array into multiple rows (often used withsplit()).str_to_map()– parses a string of key-value pairs into a map.
Choose split() when you need to break a string into an array for further transformation. Use regexp_extract() when you only need one specific part and the pattern is complex.
Performance Considerations
Using split() on large datasets is generally efficient because it’s a built-in Spark function that operates in parallel. However, avoid splitting strings in a UDF (user-defined function) if possible, as UDFs can be slower. Also, be mindful of the regex complexity – overly complex patterns can slow down processing. If you’re splitting on a simple literal, consider using split(str, literal, -1)? Actually, the third parameter in some versions limits the number of splits, but in Databricks SQL, the syntax is split(str, regex [, limit]). The limit is optional; a positive limit limits the number of splits, and a negative limit (default) means no limit. For example, split('a,b,c', ',', 2) returns ["a", "b,c"].
For very large strings, splitting can increase memory usage because arrays are stored in memory. If you only need a few elements, consider using regexp_extract() or substring_index() (which exists in MySQL but not in Spark SQL – in Spark you can use split() and index).
Frequently Asked Questions
How do I split a string into multiple rows in Databricks?
Use the explode() function on the array returned by split(). For example, in SQL: SELECT explode(split('a,b,c', ',')) AS value; This returns three rows with values ‘a’, ‘b’, ‘c’. In PySpark, use df.select(explode(split(df.col, ',')).alias("value")).
Can I split on a literal pipe character?
Yes, but you must escape it because pipe is a regex alternation operator. Use split('a|b', '\|') in SQL or split(df.col, '\|') in PySpark. Alternatively, you can use a character class: split('a|b', '[|]').
What if my delimiter is a newline or tab?
You can use the escape sequences in the regex. For a newline, use \n; for a tab, use \t. For example, split('a\nb', '\n') splits on newline. In PySpark, you might need to use a raw string: split(df.col, '\n').
Does split() work on arrays or maps?
No, split() only works on strings. If you have an array and need to split each element, you can use a higher-order function like transform() to apply split() to each element.
How do I get the last element after splitting?
You can use the element_at() function with a negative index, or use reverse(). For example, element_at(split('a,b,c', ','), -1) returns ‘c’. In PySpark, you can use split(df.col, ',').getItem(-1)? Actually, getItem() supports negative indices in recent versions, but it’s safer to use element_at() in SQL.
Is split() case-sensitive?
Yes, the delimiter matching is case-sensitive by default. If you need case-insensitive splitting, you can use a regex with the (?i) flag, like split('aBc', '(?i)b') to split on ‘b’ or ‘B’.
Conclusion
The split() function in Databricks is a versatile tool for string manipulation. Whether you’re using SQL or PySpark, mastering it will help you parse and transform text data efficiently. Remember to escape regex special characters, consider edge cases, and combine with functions like explode() for powerful data processing.
Related
- C++ String Data Type: How std::string Works
- Snowflake Split String Into Columns: SQL Methods
- Snowflake SPLIT Syntax: Arrays, Examples, and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Oracle SQL String Aggregate Functions: LISTAGG GuideOracle SQL’s main string aggregate function is LISTAGG: it combines values from multiple rows into one delimited string,…
- Excel Formula to Round Up to the Nearest Thousand in ExcelTo round a number in cell A2 up to the next thousand in Excel, use =ROUNDUP(A2,-3). The negative…
- Compare Dates in Excel IFS: Formulas and ExamplesTo compare a date in Excel with today, put the date in A2 and enter =IFS(A2<TODAY(),"Overdue",A2=TODAY(),"Due today",A2>TODAY(),"Upcoming"). IFS…
- Excel Filter Out Errors: Quick Methods That WorkTo filter out error rows in Excel for Microsoft 365 on Windows, select your data, choose Data >…
- Excel Sum Between Dates: SUMIFS Formulas and ExamplesIf dates are in A2:A100, amounts are in B2:B100, and the start and end dates are in E2…
- COUNTIF Excel Criteria Examples and How to Use ThemExcel’s COUNTIF function counts cells that meet one condition. With A2:A9 containing Paid, Pending, Paid, Overdue, paid, Pending,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.