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: