Splitting a String into Multiple Rows

Issue

If your data contains multiple items in a single column, you may need to break each string into multiple rows. 

How to Fix

Apply this stored procedure to the DataSet, which will break a string into multiple rows based on a delimiter that you set.

To split a string across multiple rows,

1. Create a new MySQL DataFlow.

2. Add your DataSet.

3. Create a new transform.

4. Copy and paste the following code snippet into the new transform:

procedure_transform_1.sql

5. Deselect “Generate Output Table” in the transform.

6. Click “Run SQL.”

7. Click “Apply” after it is done running.

8. Create a new transform.

9. Copy and paste the following code snippet into the new transform:

call_transform_2.sql

10. Deselect “Generate Output Table” in the transform.

11. Edit the statement to include:

• The name of your table.
• The column that needs to be split.
• The delimiter used to separate items in the column.

For example, if your table is named “product_tags” and the column to be split is called “tags” and the items in the “tags” column are separated by commas, you would modify the code to look like the following:

CALL string_split_procedure('product_tags', '`tags`', ',');

12. Deselect “Generate Output Table” in the transform.

13. Click “Run SQL.”

14. Click “Apply” after it is done running.

A new table called “final” has been created, which contains the data from your original table with new rows that each list one item from the original column. There will also be a new column at the end of each record in the table that contains the original string (displays as it appeared before it was split, just in case you need it).

You can now use the “final” table in subsequent transforms, including Output DataSets transforms.