split_part function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 11.3 LTS and above

Splits str around occurrences of delim and returns the partNum part.

Syntax

split_part(str, delim, partNum)

Arguments

  • str: A STRING expression to be split.
  • delimiter: A STRING expression serving as delimiter for the parts.
  • partNum: An INTEGER expression electing the part to be returned.

Returns

A STRING.

If partNum >= 1: The partNums part counting from the beginning of str will be returned.

If partNum <= -1: The abs(partNum)s part counting from the end of str will be returned.

If partNum is beyond the number of parts in str: The function returns an empty string.

If partNum is 0: split_part raises an INVALID_INDEX_OF_ZERO.

Examples

> SELECT '->' || split_part('Hello,world,!', ',', 1) || '<-';
  ->Hello<-

> SELECT '->' || split_part('Hello,world,!', ',', 2) || '<-';
  ->world<-

> SELECT '->' || split_part('Hello,world,!', ',', 100) || '<-';
  -><-

> SELECT '->' || split_part('Hello,world,!', ',', -2) || '<-';
  ->world<-

> SELECT '->' || split_part('Hello,world,!', ',', -100) || '<-';
  -><-

> SELECT '->' || split_part('', ',', 1) || '<-';
  -><-

> SELECT '->' || split_part('Hello', '', 3) || '<-';
  -><-

> SELECT '->' || split_part('Hello,World,!', ',', 0) || '<-';
  ERROR: INVALID_INDEX_OF_ZERO

> SELECT split_part('5A6B7' COLLATE UTF8_BINARY, 'a', 1);
  5A6B7

> SELECT split_part('5A6B7' COLLATE UTF8_LCASE, 'a', 1);
  5