Split string into multiple rows in SQL

Question

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.

I understand this to be a onetime job - rightly so?

Eugen Rieck
– Eugen Rieck

2012-01-05 12:00:53 +00:00
Commented Jan 5, 2012 at 12:00 — Eugen Rieck
– Eugen Rieck, Commented Jan 5, 2012 at 12:00
possible duplicate of Mysql string split

Michał Powaga
– Michał Powaga

2012-01-05 12:01:07 +00:00
Commented Jan 5, 2012 at 12:01 — Michał Powaga
– Michał Powaga, Commented Jan 5, 2012 at 12:01
Yes Eugen its a one time job.

user1132038
– user1132038

2012-01-05 12:06:32 +00:00
Commented Jan 5, 2012 at 12:06 — user1132038
– user1132038, Commented Jan 5, 2012 at 12:06

Eugen Rieck · Accepted Answer · 2012-01-05 12:46:57Z

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.

edited Jan 5, 2012 at 12:46

answered Jan 5, 2012 at 12:11

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1132038 Over a year ago

Apart from adding wildcards to the SQL statement and adding a " to the insert line, this worked perfectly. Thanks alot!

Eugen Rieck Over a year ago

Good catch, I corrected the missing quote and wildcards, sorry for that, a bad case of "thinking about line n+1 while typing line n"

reinierpost Over a year ago

The question is how to do it in SQL.

SamMeow · Accepted Answer · 2019-02-14 08:24:32Z

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/

Collectives™ on Stack Overflow

Split string into multiple rows in SQL

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related