split function

Applies to: check marked yes Databricks SQL check marked yes 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,]