Databricks SQL SPLIT Function: Syntax and Examples

Formulas & functions · Updated November 22, 2026

In Databricks SQL, split(str, regex [, limit]) divides a string wherever it matches a regular expression and returns the pieces as an array of strings. Use it when a field contains consistently delimited values; remember that the delimiter argument is a regex, not always a literal character.

What SPLIT Returns

split converts one string into an array, a collection whose individual values can be selected, counted, or expanded into rows. For example, splitting red,green,blue on a comma produces an array with three elements: red, green, and blue. The original string is not changed; the function produces a result you can use in a query.

This distinction matters because the result is not another delimited string. It is an array value. You can pass it to array functions, select a particular element, or use a generator such as explode to produce one row per element. If you only need one known field rather than the entire array, a function such as split_part may be simpler.

A basic query looks like this:

SELECT split('red,green,blue', ',') AS colors;

The result in the colors column is an array, displayed in Databricks SQL as values resembling [red, green, blue]. The returned array type is ARRAY<STRING>.

Syntax and Arguments

The general form is split(str, regex [, limit]). The first argument is the input string, the second is a regular-expression pattern that identifies where to split, and the optional third argument limits the number of splits. The brackets in the syntax indicate that limit is optional; they are not typed into the query.

  • str is the string expression to divide. It can be a column, a literal, or the result of another expression.
  • regex is a string containing a regular expression. The matched text is used as the separator and is not included as an array element.
  • limit is an integer expression. A positive value caps the number of resulting pieces; when the cap is reached, the final piece contains the unsplit remainder.

For instance, splitting a timestamp-like value on hyphens does not require an elaborate pattern:

SELECT split('2026-11-03', '-') AS date_parts;

This returns three strings. The function does not infer that they represent a year, month, and day; it simply separates the input according to the pattern. Any conversion to date or numeric types is a separate step and should be based on the data format you expect.

Why the Delimiter Is a Regular Expression

Many common separators, including commas and hyphens, can be supplied directly as patterns. Other characters have special meaning in regular expressions. A period, for example, matches any character, so split(value, '.') does not mean split on literal periods. Use a character class such as [.] to match a period literally:

SELECT split('archive.tar.gz', '[.]') AS file_parts;

Other useful examples include [|] for a literal vertical bar and [+] for a literal plus sign. Character classes make the intent clear and avoid confusion about escaping backslashes through both SQL string-literal rules and regular-expression rules. For a delimiter made up of multiple characters, write a pattern that matches that exact sequence; a pattern such as -- matches two consecutive hyphens.

Because the pattern is a regex, it can also describe a group of separators. For example, [,;] matches either a comma or a semicolon. A pattern such as + matches one or more ordinary spaces, which can be useful for text separated by repeated spaces. Do not make a pattern broader than the input format warrants: splitting on all punctuation, for example, can break values that legitimately contain punctuation.

Using the Limit Argument

When the input has a predictable structure and you only want a fixed number of splits, provide a positive limit. The limit controls how many pieces the operation returns; if the input has more matching separators, the last returned piece keeps the remaining text rather than splitting it again.

SELECT split('account:region:note:with:colons', ':', 3) AS fields;

Here the result has at most three elements: account, region, and note:with:colons. This can be useful when the first separators have structural meaning but the final field may itself contain the delimiter. For example, a log format might reserve the first two colons for an identifier and a region, with the rest belonging to a message.

Without a limit, the function separates at each match in the input. A limit is not a way to validate the input, however. If the expected separator is missing, or appears fewer times than expected, the array will have fewer elements. Check the data shape before assuming every row has the same number of fields.

Accessing Array Elements and Expanding Rows

Use element_at when you want one element from the array. Its array positions are one-based: position 1 means the first value, position 2 the second, and so on. For a column named full_name containing a first and last name separated by a space, the second piece can be selected with:

SELECT element_at(split(full_name, ' '), 2) AS second_piece
FROM people;

This example is intentionally simple: it assumes the input has the expected spacing and at least two pieces. Real names can contain middle names, multiple spaces, or only one word. A query that treats the second piece as a family name should account for those cases rather than treating the split itself as validation.

To turn every piece into its own row, use explode with the array. For example:

SELECT explode(split('priority,review,ready', ',')) AS tag;

The query produces one row per tag. Applied to a table, this pattern is useful for turning a delimited tags column into rows that can be filtered, grouped, or joined. Be aware that expanding arrays changes row counts: a source record with four pieces contributes four output rows. Preserve an identifier in the same query when you need to trace each output value back to its source record.

Practical Examples

A common use is extracting values from a simple, consistently formatted field. A pipe-separated list can be split using a character class:

SELECT split('north|central|south', '[|]') AS zones;

Another use is separating text on more than one possible delimiter. This example treats commas and semicolons as equivalent separators:

SELECT split('apples,pears;plums', '[,;]') AS produce;

These examples work because the data format says those characters mark boundaries. If a comma can also appear inside a quoted value, a basic split is not a reliable CSV parser. For example, splitting North, America on commas would divide the place name too. Use a CSV-aware reader or parse the structured source appropriately when quoting and escaping rules matter.

You can combine splitting with other expressions, too. Trimming an input before splitting can remove unwanted spaces at the ends of the whole string, but it does not automatically trim every resulting element. If the source is red, green, the second array value may still begin with a space. Decide whether whitespace belongs in the values, or clean each element as part of a deliberate transformation.

Nulls, Empty Values, and Data Quality

A null input is not the same as an empty string or a string containing separators. In general, a null string expression yields a null result, whereas a non-null string is processed according to its contents. Empty pieces can also arise when separators occur next to one another or at the edges of a string. Those cases can matter in data cleaning: an empty element may mean a missing field, an accidental extra delimiter, or a legitimate blank value.

Before relying on a particular position, inspect the values your source actually contains. Useful checks include counting array elements with size(split(column, ',')), finding rows with unexpected counts, and reviewing examples where the input is null, empty, or ends with a delimiter. The expected count depends on the data contract. A two-field format should not silently be treated as valid when a row produces only one piece.

There is a further distinction between splitting and parsing. split does not validate a schema, convert data types, interpret quoted delimiters, or report that a record is malformed. It performs a regex-based division. If downstream logic depends on a date, number, or identifier being valid, use an explicit conversion and validation strategy after splitting, and decide how invalid rows should be handled.

Choosing Between SPLIT and Related Functions

Choose split when you need an array of all pieces or want to process each piece later. It is also suitable when the separator is a regex pattern, such as either of two punctuation characters. If you need only one particular section, consider split_part, which returns a selected part as a string instead of returning an array for further work. Check its position-number rules and behavior for missing parts against the Databricks SQL documentation for your environment.

If your input is a properly structured JSON value, use JSON-reading or JSON-extraction functions rather than cutting the text at punctuation. Braces, commas, and quotes are part of JSON syntax and can appear in ways a simple delimiter split will not understand. Similarly, use a CSV-aware ingestion or parsing process for quoted CSV fields.

  • Use split when you want the pieces as an array, need to use a regex separator, or plan to expand the values.
  • Use split_part when the task is simply to retrieve one position from a string separated by a known delimiter.
  • Use a format-aware parser when the input has quoting, escaping, nesting, or a formal schema.

Frequently Asked Questions

Does Databricks SQL SPLIT use a regular expression?
Yes. Its second argument is a regex pattern. A comma can be passed as ',', but regex metacharacters such as a period should be written in a literal-matching form such as [.].

What data type does SPLIT return?
It returns an array of strings, with the SQL type ARRAY<STRING>. Use array functions to work with its elements, or a generator such as explode to turn them into rows.

How do I split on a pipe character?
Use a pattern that treats the pipe literally, such as [|]: split(value, '[|]'). A bare pipe has special meaning in regex syntax and should not be treated casually as an ordinary separator.

How do I get the second value after splitting?
Use element_at(split(value, ','), 2). element_at uses one-based positions for arrays, so 2 refers to the second element. Make sure the input has enough pieces for the operation you intend.

Can SPLIT correctly parse CSV or quoted text?
Not by itself. It divides wherever its regex matches and does not understand quoted separators or CSV escaping. Use a CSV-aware parser when delimiters can occur inside quoted fields.

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.