Thread: Split String Into Multiple Records

Is there a good way to split a string into multiple records?<br /><br />Here is what I am trying to do...<br /><br />I
havea table "branch" with a column "branch_num" which has a comma delimited list of numbers - the users weren't
supposedto do this but they did and now I have to fix it.  We want to create a new table "branch_area" and move this
commadelimited list into this new table as multiple records before dropping the "branch_num" from the "branch" table.
<br/><br />branch {<br />    branch_id bigserial primary key,<br />    branch_num varchar(255)<br />}<br /><br
/>branch_area{<br />    branch_area_id bigserial primary key,<br />    branch_id bigint foreign key to branch,<br />   
branch_numvarchar(10) <br />}<br /><br />I want to migrate the data something like this:<br /><br />insert into
branch_area<br/>(branch_id, branch_num)<br />select<br />    branch_id,<br />    -- This is the part I need help with
->split branch.branch_num on ',' <br />from branch<br />;<br /><br />Is there a good way (or alternative way) to do
this?<br/><br />Thanks!<br clear="all" /><br />-- <br
/>==================================================================<br/>   Aaron Bono<br />   Aranya Software
Technologies,Inc. <br />   <a href="http://www.aranya.com">http://www.aranya.com</a><br />   <a
href="http://codeelixir.com">http://codeelixir.com</a><br
/>================================================================== 

"Aaron Bono" <postgresql@aranya.com> 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

On 4/21/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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
==================================================================