Select Split Parts

In Split, we saw how to split each string element in a table column into parts around a given string delimiter pattern. We will see in this section how to select one, all, or some of the parts resulting from splitting a string.

This section is organized as follows:

  • One Part; where we cover how to select any one part from the parts obtained by splitting a string given its index.
  • All Parts; where we cover how to select all parts obtained from splitting a string.
  • Some Parts; where we cover how to select some parts returned by splitting a string.

This section is complemented by:

  • Split an input string around a delimiter which may be a substring or a regex pattern e.g. break a sentence into words.
  • Process: An optional step where we process the selected parts resulting from the prior selection process e.g. concatenate the first and third parts with an underscore in between.

One Part

We wish to select any one part from the parts obtained by splitting a string given its index.

In this example, we wish to split each element in the column col_1 around the hyphen ‘-’ character and return the second part.

SELECT col_1,
       SPLIT(col_1, '-')[SAFE_OFFSET(1)] AS col_2
FROM table_1;

Here is how this works:

  • We use SPLIT() to return an array of parts for each element in col_1. See Split.
  • We use [SAFE_OFFSET(1)] to select the second element of the array returned by SPLIT().

Extension: Select Last Part

In this example, we wish to split each element in the column col_1 around the hyphen ‘-’ character and return the last part.

SELECT col_1,
       SPLIT(col_1, '-')[SAFE_OFFSET(ARRAY_LENGTH(SPLIT(col_1, '-')) - 1)] AS col_2
FROM table_1;

Here is how this works:

  • To get the last element of an array we get the array length and subtract one to use it as an index if the SAFE_OFFSET() function. See Working with Arrays.

All Parts

We wish to select all parts obtained from splitting a string.

In this example, we wish to split each element of the column col_1 around the hyphen character ‘-’ and store each part in a new column. We expect three parts, and we wish to call the three resulting columns 'col_2', 'col_3', and 'col_4'.

SELECT col_1,
       SPLIT(col_1, '-')[SAFE_OFFSET(0)] AS col_2,
       SPLIT(col_1, '-')[SAFE_OFFSET(1)] AS col_3,
       SPLIT(col_1, '-')[SAFE_OFFSET(2)] AS col_4
FROM table_1;

Here is how this works:

  • This works similar to the above scenario except we create a new column for each part.

Some Parts

We wish to select some parts returned by splitting a string.

In this example, we wish to select parts 1 and 3 resulting from splitting each element of the column col_1 around the hyphen character ‘-’ as new columns.

SELECT col_1,
       SPLIT(col_1, '-')[SAFE_OFFSET(0)] AS col_2,
       SPLIT(col_1, '-')[SAFE_OFFSET(2)] AS col_3
FROM table_1;

Here is how this works:

  • This works similarly to All Parts scenario above except we only select two elements of the array.
SQL
I/O