split function

Applies to: Databricks SQL Databricks Runtime

Splits str around occurrences that match regex and returns an array with a length of at most limit.

Syntax

split(str, regex [, limit] )

Arguments

• str: A STRING expression to be split.
• regexp: A STRING expression that is a Java regular expression used to split str.
• limit: An optional INTEGER expression defaulting to 0 (no limit).

Returns

An ARRAY<STRING>.

If limit > 0: The resulting array's length will not be more than limit, and the resulting array's last entry will contain all input beyond the last matched regex.

If limit <= 0: regex will be applied as many times as possible, and the resulting array can be of any size.

Examples

> SELECT split('oneAtwoBthreeC', '[ABC]');
 [one,two,three,]

> SELECT split('oneAtwoBthreeC', '[ABC]', -1);
 [one,two,three,]

> SELECT split('oneAtwoBthreeC', '[ABC]', 2);
 [one,twoBthreeC]

> SELECT split('oneAtwoBthreeC' COLLATE UTF8_BINARY, '[abc]');
 [oneAtwoBthreeC]

> SELECT split('oneAtwoBthreeC' COLLATE UTF8_LCASE, '[abc]');
 [one,two,three,]

Related functions

• regexp_extract function
• regexp_extract_all function
• split_part function