Split a comma-separated record into its own rows

All we need is an easy explanation of the problem, so here it is.

I have the following table, named stores:

store_id INT
emails VARCHAR

That contains values:

And I want to generate the following set:

As you can see I want to split the emails field into a separate record containing only one email address.
Do you have any idea how I can do that?

So far I managed to create the following query:

select store_id,string_to_array(emails,',') from stores

But I don’t know how I can split the string_to_array to its own row.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You need to use UNNEST() in conjunction with STRING_TO_ARRAY() as follows (fiddle available here):

CREATE TABLE stores
(
  store_id INTEGER NOT NULL,
  emails TEXT NOT NULL
);

Populate:

INSERT INTO stores
VALUES
(1, '[email protected], [email protected]'),
(2, '[email protected], [email protected]'),
(4, '[email protected],[email protected]');

And then run this query:

SELECT 
  store_id,
  UNNEST(STRING_TO_ARRAY(emails, ',')) AS email
FROM stores;

Result:

store_id    email
1   [email protected]
1    [email protected]
2   [email protected]
2    [email protected]
4   [email protected]
4   [email protected]

One word of caution – depending on how your emails are formatted in the string, you might want to use the TRIM() function to make sure that there are no preceding or trailing spaces in the emails (shown in fiddle).

You can dissect what’s happening if you simply run:

SELECT 
  store_id,
  STRING_TO_ARRAY(emails, ',') AS email_array
FROM stores;

Result (see updated fiddle) – the emails are still just one record – UNNEST puts each separate element of the array into a different row!:

store_id    email_array
       1    {[email protected]," [email protected]"}
       2    {[email protected]," [email protected]"}
       4    {[email protected],[email protected]}

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply