How can I split a concatenated string to a new column-CodePudding

I have a column that contains concatenated string. I want to split them to a new column.

Work Rate
Medium / High
Medium / Medium
Medium / Medium
High / Medium
Medium / Medium
Medium / Low
Medium / Low
Low / High
High / High
Medium / Medium
High / Low

I found something like that. But it does not create a column. Also, as a second question, can I create a column from a query like this one?

SELECT *,split_part("Work_Rate",'/',1) as attack_work,
split_part("Work_Rate",'/',2) AS defence_work FROM "Football";

CodePudding user response：

You would need to:

BEGIN;
ALTER TABLE  "Football" ADD COLUMN attack_work varchar;
ALTER TABLE  "Football" ADD COLUMN defence_work varchar;
COMMIT; --Or ROLLBACK; if it fails

Then:

BEGIN;
UPDATE
    "Football"
SET
    attack_work = split_part("Work_Rate",'/',1),
    defence_work = split_part("Work_Rate",'/',2);
COMMIT; --Or ROLLBACK; if it fails