How to split values in a cell separated by a specific character
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:
-
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:
Please sign in to leave a comment.
Comments
1 comment