Split a string into multiple rows in SQL

Question

I inherited the database, and trying to make it cleaner and more useful, I ran into the following problem.

After moving the column of files to a separate table, I now have the task of dividing these files into different rows. See my example below.

key | jobid | files | -------------------------------------------- 1 30012 file1.pdf;file2.pdf 2 30013 file3.pdf 3 30014 file4.pdf;file5.pdf;file6.pdf 

I need an SQL statement that will make the table as follows:

 key | jobid | files | -------------------------------------------- 1 30012 file1.pdf 2 30013 file3.pdf 3 30014 file4.pdf 4 30012 file2.pdf 5 30014 file5.pdf 6 30014 file6.pdf 

It is not important that the source records are deleted to achieve this, so the following solution would be acceptable:

 key | jobid | files | -------------------------------------------- 4 30012 file1.pdf 5 30013 file3.pdf 6 30014 file4.pdf 7 30012 file2.pdf 8 30014 file5.pdf 9 30014 file6.pdf 

Basically, I just need a string of strings divided by; delimiter and newline created using split lines.

Any help you can provide will be appreciated.

Answer 1

In PHP (assuming $ db is a valid db connection and key is auto_increment):

 $sql="select `key`, jobid, files from filestable where files like '%\\;%'"; $qry=mysql_query($sql,$db); $sql=array(); while (true) { $row=mysql_fetch_row($qry); if (!$row) break; $key=$row[0]; $jobid=$row[1]; $files=explode(';',$row[2]); foreach ($files as $file) { $file=mysql_real_escape_string($file,$db); $sql[]="insert into filestable (jobid,files) values ($jobid,'$file')"; } $sql[]="delete from filestables where `key`=$key"; } 

now $ sql has an array of SQL statements to run - either run them at the end of the while loop, or batch release them, write them later, regardless of what matches your loading pattern.