[SQL] Split String Into Multiple Records
Is there a good way to split a string into multiple records?
Here is what I am trying to do...
I have a table "branch" with a column "branch_num" which has a comma
delimited list of numbers - the users weren't supposed to do this but they
did and now I have to fix it. We want to create a new table "branch_area"
and move this comma delimited list into this new table as multiple records
before dropping the "branch_num" from the "branch" table.
branch {
branch_id bigserial primary key,
branch_num varchar(255)
}
branch_area {
branch_area_id bigserial primary key,
branch_id bigint foreign key to branch,
branch_num varchar(10)
}
I want to migrate the data something like this:
insert into branch_area
(branch_id, branch_num)
select
branch_id,
-- This is the part I need help with -> split branch.branch_num on ','
from branch
;
Is there a good way (or alternative way) to do this?
Thanks!
--
==
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==
Re: [SQL] Split String Into Multiple Records
"Aaron Bono" <[EMAIL PROTECTED]> writes: > Is there a good way to split a string into multiple records? > I have a table "branch" with a column "branch_num" which has a comma > delimited list of numbers - the users weren't supposed to do this but they > did and now I have to fix it. We want to create a new table "branch_area" > and move this comma delimited list into this new table as multiple records > before dropping the "branch_num" from the "branch" table. 8.3 will have some nifty functions for that, but in existing releases you're on your own. I'd think about making a plpgsql function that loops around split_part() and returns each chunk with RETURN NEXT. One problem with that is that you'd want to invoke it like so: insert into branch_area ... select my_split_func(branch_num) from branch; and plpgsql doesn't support invoking set-returning functions this way. But you can get around that with a SQL-language wrapper function. It's pretty grotty on the whole, but should do for a one-time problem. BTW, check the archives, because I think this type of problem has been discussed before --- somebody may have already posted usable code. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Split String Into Multiple Records
On 4/21/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Aaron Bono" <[EMAIL PROTECTED]> writes: > Is there a good way to split a string into multiple records? > I have a table "branch" with a column "branch_num" which has a comma > delimited list of numbers - the users weren't supposed to do this but they > did and now I have to fix it. We want to create a new table "branch_area" > and move this comma delimited list into this new table as multiple records > before dropping the "branch_num" from the "branch" table. 8.3 will have some nifty functions for that, but in existing releases you're on your own. I'd think about making a plpgsql function that loops around split_part() and returns each chunk with RETURN NEXT. One problem with that is that you'd want to invoke it like so: insert into branch_area ... select my_split_func(branch_num) from branch; and plpgsql doesn't support invoking set-returning functions this way. But you can get around that with a SQL-language wrapper function. It's pretty grotty on the whole, but should do for a one-time problem. BTW, check the archives, because I think this type of problem has been discussed before --- somebody may have already posted usable code. I'll just do it by hand. There isn't much data right now. I will keep an eye open for the new features though. Thanks! -- == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ==
