Split to rows
Turn a list stored in one text cell into one row per item. Use it to work with each tag or list item on its own, while keeping it linked to the rest of its original row.
Split tags into rows
Manual Input has four rows with id and tags columns. Split to rows separates tags at each comma, producing six rows with one tag per row.
Split to rows interactive example
Loading interactive example…
The live example needs a desktop screen. The result it produces is shown here, and the explanation and settings below work on any screen.
| id | tags |
|---|---|
| 1 | red |
| 1 | blue |
| 2 | blue |
| 3 | green |
| 3 | red |
| 4 |
- Change Splitter to
Pipe (|). None of the values contain a pipe, so the output returns to four rows with the original tags intact. - Choose
Customin Splitter and enter a comma in Pattern. The output returns to six rows.
When to use
Use Split to rows when a cell holds a list, such as several tags, and each item should be its own row. For a fixed set of parts, use Split to columns.
Read the output
The output has the same two columns, id and tags, but six rows:
| id | tags |
|---|---|
| 1 | red |
| 1 | blue |
| 2 | blue |
| 3 | green |
| 3 | red |
| 4 | NULL |
The original tags value is replaced by each part. Other columns repeat for every part, so both red and blue remain linked to id 1. The block doesn't add a separate column containing the original list.
Parts stay in their original order within each input row, and input rows keep their relative order. The missing tags value stays as one NULL row. An empty string is also kept, but it's a text value rather than a missing value.
To put the parts in separate columns instead, use Split to columns.
To separate repeated values from unique values after splitting, use Unique.
Inputs and outputs
Inputs
1 port- InRequired
Accepts: Tables, Geospatial data.
Accepts one connection on this port.
Outputs
1 port- Out
Configure the block
Choose the text column and the literal separator between its items.
- Column
Choose the text column to split. The first text column is selected automatically. Each part replaces the original value in this column.
- Splitter
Choose Comma (,), Pipe (|), Tab, Semicolon (;), Space or Custom. The separator is matched exactly and removed from the resulting parts. Default: Comma (,).
- Pattern
Shown for Custom. Enter a nonempty literal separator, such as ::. It can contain multiple characters and isn't a regular expression.
Split to rows FAQs
- What happens when a value has no separator?
- It stays on one row unchanged. In the example, blue stays one row for id 2.
- Are missing values removed?
- No. A NULL value produces one row with NULL in the split column.
- Are spaces or empty parts removed?
- No. Splitting red, blue on a comma leaves a leading space before blue. Consecutive or trailing separators produce empty strings on their own rows.
- Are duplicate parts removed?
- No. Every part produces a row, even if the same value appears more than once.
Prepare your data. Build it in Strata.
Strata opens in your browser, with no account.