MySQL: Need help splitting a field 1

[segana]

Hi,
First time posting, but i have found this forum really useful in the past.
The issue i have is that i need to be able to take information from a single field and split it into many fields within another table.

I'm developing new processes for an all ready exisiting system which has it's own built in objects.

One of these objects allows you to check various options in a list, but then it saves the values in a single field in the example form of '1,6,7,11,15' (these are the id's of each option.

The problem is, i need to be able to update another table based on each value in the field.

I need a way of taking each value and entering it into a seperate field as follows:

Field1
1,3,6,10

SPLIT INTO:

Field1 | Field2 | Field3 | Field4
1 3 6 10

The MySQL Server Version is 4.1.8 and unfortunately this has to be done in only SQL.

I know having multiple entries held in a single field is just plain dumb...but i didn't design the original system and at the moment i don't have a way to re-write the code to change this, so i'm just having to work with it.

Have seen various other threads that show how to do something similar but only when the field contains 2 values (e.g. first_name last_name) or they've shown how to do this using PHP (which is not an option for me)

Any help in this is really, really appreciated.

 

[TonyGroves]

You can split the field using the SUBSTRING_INDEX function:
Value 1:[tt] SUBSTRING_INDEX(bigfield,',',1)[/tt]
Value 2:[tt] SUBSTRING_INDEX(SUBSTRING_INDEX(bigfield,',',2),',',-1)[/tt]
Value 3:[tt] SUBSTRING_INDEX(SUBSTRING_INDEX(bigfield,',',3),',',-1)[/tt]
etc.

 

[segana]

Hi Tony,

Thanks for your reply.

That's done the trick, just need to loop it now.

Have never used a SUBSTRING_INDEX before (yes, i really am that new lol)

Thanks again, much appreciated.

Matt