Split string to multiple column

Question

|assoc_name|
------------
|meena,malhotra
|sita,sharma
|assoc_name|assoc_last_name|
----------------------------
|meena | malhotra |
|sita | sharma |

SELECT split_part(assoc_name,',','1') AS Part1 from mytable; This worked but ho do i save the output to a different column?

Answer 1

Just make two calls to SPLIT_PART, one for each desired column:

SELECT
 SPLIT_PART(assoc_name, ',', 1) AS assoc_name,
 SPLIT_PART(assoc_name, ',', 2) AS assoc_last_name
FROM mytable;

If you want to persist this view in a database table, then try using INSERT INTO ... SELECT, using the above select:

INSERT INTO someOtherTable (assoc_name, assoc_last_name)
SELECT
 SPLIT_PART(assoc_name, ',', 1),
 SPLIT_PART(assoc_name, ',', 2)
FROM mytable;

To handle the case where one/both of the above calls to SPLIT_PART might return NULL, and the target column be non nullable, then consider using COALESCE:

INSERT INTO someOtherTable (assoc_name, assoc_last_name)
SELECT
 COALESCE(SPLIT_PART(assoc_name, ',', 1), ''),
 COALESCE(SPLIT_PART(assoc_name, ',', 2), '')
FROM mytable;

• Dimple Mathew: that dint get saved to my db
• Dimple Mathew: I have a column assoc_last_name.. I want to split and save the value
• Tim Biegeleisen: @DimpleMathew Check my updated answer. Just insert this new information into another table.
• Dimple Mathew: Hey its throwing error Failing row contains (Gowrisankar, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, Hemalatha).
• Tim Biegeleisen: Then you're not running the code in my answer, which involves only two columns.