split_part
function
Applies to: Databricks SQL Databricks Runtime 11.3 LTS and above
Splits str
around occurrences of delim
and returns the partNum
part.
Arguments
str
: ASTRING
expression to be split.delimiter
: ASTRING
expression serving as delimiter for the parts.partNum
: AnINTEGER
expression electing the part to be returned.
Returns
A STRING
.
If partNum
>= 1: The partNum
s 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