Function to seperate comma delimited db field into new table

Hi All,
I hope someone can help, i have taken on a project from someone and
have been given their db to work from, one thing they have done which i
know is bad design is seperate a lit of locations into one field with
commas, i.e 1,5,8,9. I now want to create a proper relationship by
adding a new relation table..
So instead of :
JobID LocationID
1 1,2,3
I will have in a new table:
JobID LocationID
1 1
1 2
1 3
Is there a function i can use or create to iterate through these old
values and insert them into my new table, the table is already to big
to do it manually, i only need to run it once so performance isnt an
issue..
Could someone point me in the right direction of where to search or a
start as i although i know a bit about sql this is above my head at the
mo..
Cheers
Anthonyhttp://www.aspfaq.com/2248
<anthonykallay@.hotmail.com> wrote in message
news:1133789661.982222.291240@.o13g2000cwo.googlegroups.com...
> Hi All,
> I hope someone can help, i have taken on a project from someone and
> have been given their db to work from, one thing they have done which i
> know is bad design is seperate a lit of locations into one field with
> commas, i.e 1,5,8,9. I now want to create a proper relationship by
> adding a new relation table..
> So instead of :
> JobID LocationID
> 1 1,2,3
> I will have in a new table:
> JobID LocationID
> 1 1
> 1 2
> 1 3
> Is there a function i can use or create to iterate through these old
> values and insert them into my new table, the table is already to big
> to do it manually, i only need to run it once so performance isnt an
> issue..
> Could someone point me in the right direction of where to search or a
> start as i although i know a bit about sql this is above my head at the
> mo..
> Cheers
> Anthony
>|||My personal favourite (by Dejan Sarka):
http://solidqualitylearning.com/blo.../10/22/200.aspx
ML
http://milambda.blogspot.com/|||Another possible option...
http://searchsqlserver.techtarget.c...1074960,00.html
"anthonykallay@.hotmail.com" wrote:

> Hi All,
> I hope someone can help, i have taken on a project from someone and
> have been given their db to work from, one thing they have done which i
> know is bad design is seperate a lit of locations into one field with
> commas, i.e 1,5,8,9. I now want to create a proper relationship by
> adding a new relation table..
> So instead of :
> JobID LocationID
> 1 1,2,3
> I will have in a new table:
> JobID LocationID
> 1 1
> 1 2
> 1 3
> Is there a function i can use or create to iterate through these old
> values and insert them into my new table, the table is already to big
> to do it manually, i only need to run it once so performance isnt an
> issue..
> Could someone point me in the right direction of where to search or a
> start as i although i know a bit about sql this is above my head at the
> mo..
> Cheers
> Anthony
>