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

Follow

Scott Bowen

• July 10, 2019 17:38

Sometimes the data coming from an export or into a database will have some cells that contain a string of data separated by commas or another character. That data isn't very useful unless you can split it apart into separate rows or columns. Basically you need to specify the character used as a delimiter, like when you use the "Text to Columns" function in Excel.

There are a lot of ways you can use SPLIT_PART and other functions in Postgres to get this to work. Here are two examples of ways you can split the data from a cell into rows and columns.

Here is an example of why you would need to use the query:

This first example lets you select one of the values from the string. In this example, each row has different values that use the same syntax: a name, three numbers, and a state, all separated by a comma. I can use the SPLIT_PART function to specify which part of the string I want to separate out into a new column:

SELECT
SPLIT_PART("Names2", ',', 1) AS "name",
SPLIT_PART("Names2", ',', 3) AS "important_number",
SPLIT_PART("Names2", ',', 5) AS "state"
FROM report

The second example separates out each value and places it into its own row. This would give you the unique values from that cell that perhaps you could use to join with another report. Here is the sample data where this might be useful:

SELECT
SPLIT_PART("Column_name", ',', ROW_NUMBER() OVER():: INT) AS "Result_column"
FROM report

Using this query (but adding in the correct column names) I can get this result:

1

• 

  Scott Bowen

  • December 16, 2019 19:01

  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
• 

  Meghan Meredith

  • December 13, 2021 23:48

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

  1

  Comment actions Permalink
• 

  Brooks Duggan

  • December 16, 2021 22:56

  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.