How to split values in a cell separated by a specific character

Comments

3 comments

  • Avatar
    Scott Bowen

    Here's another example of splitting the data into separate rows while keeping the rest of the data from the other columns.

    Let's say you have data showing t-shirt orders over time, and one of the fields lists the individual sizes in that order:

    If you would like to give each item in it's own row, but still show the order date, order number, and email associated with the order, you can use this SQL:

    SELECT
    *,
    unnest(string_to_array("Order_items", ',')) AS "Shirt size"
    FROM report;

    And you will end up with this data:

    0
    Comment actions Permalink
  • Avatar
    Meghan Meredith

    How do you do this if the separator is not a character but just a space?

    1
    Comment actions Permalink
  • Avatar
    Brooks Duggan

    Meghan Meredith

    Hey Meghan, you can generally do this with similar code (example showed in the below link):
    https://stackoverflow.com/questions/1986491/sql-split-string-by-space-into-table-in-postgresql

    Generally, you would use (to mirror the example above):

    SELECT
    *,
    unnest(string_to_array("Order_items", ' ')) AS "Shirt size"
    FROM report;

    also, if you are not wanting it to unnest, creating duplicates of data for every value in the array...you can use:

    SELECT
    *,
    string_to_array("Order_items", ' ')[0] AS "Shirt size"
    FROM report;

    --result is the first shirt listed in the array

    SELECT
    *,
    string_to_array("Order_items", ' ')[1] AS "Shirt size"
    FROM report;
    --result is the second shirt listed in the array

    etc

    Hope this helps!

    0
    Comment actions Permalink

Please sign in to leave a comment.