4

I have inherited a database, and in my efforts to make it cleaner and more useful, I have encountered the following problem.

After moving the files column to a seperate table, I now have the task of seperating out these files into different rows. Please 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 would like an SQL statement that would make the table into the following:

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 doesnt matter if the original entrys must be deleted to achieve this, so the following solution would also 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 the files string split on the ; delimiter and a new row created with the split strings.

Any help you can provide would be appreciated.

user1132038
  • 63
  • 1
  • 6

2 Answers2

0

I have the exact same question, found an article that may help, they have provided MySQL script

create table books (tags varchar(1000));

insert into books values
    ('A, B, C, D'),
    ('D, E'),
    ('F'),
    ('G, G, H')
;

select
  TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(B.tags, ',', NS.n), ',', -1)) as tag
from (
  select 1 as n union all
  select 2 union all
  select 3 union all
  select 4 union all
  select 5 union all
  select 6 union all
  select 7 union all
  select 8 union all
  select 9 union all
  select 10
) NS
inner join books B ON NS.n <= CHAR_LENGTH(B.tags) - CHAR_LENGTH(REPLACE(B.tags, ',', '')) + 1

I've added the keyname into the playground here
https://www.db-fiddle.com/f/kLeLYVPmuoFtLEuAb8ihuE/0

Reference:
https://www.holistics.io/blog/splitting-array-string-into-rows-in-amazon-redshift-or-mysql/

Samuel Chan
  • 231
  • 1
  • 4
  • 11
0

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 them up, write them out for later, whatever fits your load pattern.

Eugen Rieck
  • 64,175
  • 10
  • 70
  • 92