Converting delimited string to multiple values in mysql

开发者_如何学GoI have a mysql legacy table which contains an client identifier and a list of items, the latter as a comma-delimited string. E.g. "xyz001", "foo,bar,baz". This is legacy stuff and the user insists on being able to edit a comma delimited string.

They now have a requirement for a report table with the above broken into separate rows, e.g.

"xyz001", "foo"
"xyz001", "bar"
"xyz001", "baz"

Breaking the string into substrings is easily doable and I have written a procedure to do this by creating a separate table, but that requires triggers to deal with deletes, updates and inserts. This query is required rarely (say once a month) but has to be absolutely up to date when it is run, so e.g. the overhead of triggers is not warranted and scheduled tasks to create the table might not be timely enough.

Is there any way to write a function to return a table or a set so that I can join the identifier with the individual items on demand?

---

This is called walking a string. Here's an example of how you might do it with the specs provided:

You'll need to create a table which contains as many integers as the length of the field + 1. So if the field's length is 255, you will need 256 records which just contain a single number from 0-255.

int_table:

+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+---+

Next, you will need a query which joins on this table and checks if a comma exists in that location or not. (I called your table legacy_table with the fields client and items, respectively.)

select 
  legacy_table.client, 
  substring(
    legacy_table.items, 
    int_table.i + 1, 
    if(
      locate(',', legacy_table.items, int_table.i + 1) = 0, 
      length(legacy_table.items) + 1, 
      locate(',', legacy_table.items, int_table.i + 1)
    ) - (int_table.i + 1)
  ) as item
from legacy_table, int_table
where legacy_table.client = 'xyz001'
  and int_table.i < length(legacy_table.items)
  and (
    (int_table.i = 0) 
    or (substring(legacy_table.items, int_table.i, 1) = ',')
  )

It may not be efficient enough for you to actually use it, but I thought I'd present it as an example just so you know what is available.

---

You can do this with a Numbers or Tally table which contains a sequential list of integers:

Select Substring(T.List, N.Value, Locate(', ', T.List + ', ', N.Value) - N.Value)
From Numbers As N
    Cross Join MyTable As T
Where N.Value <= Len(T.List)
    And Substring(', ' + T.List, N.Value, 1) = ', '

In the above case, my Numbers table is structured like so:

Create Table Numbers( Value int not null primary key )

---

Interesting.

I can't think of a clean and tidy solution, but I could offer some ideas.

I guess you could combine UNION with the following:

http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/

If you know the maximum number of items in any row, then you could do a union on the results .. something like:

(SELECT col1, SPLIT_STR(col2, ',', 1) c2 from tbl)
UNION (SELECT col1, SPLIT_STR(col2, ',', 2) c2 from tbl where col2 like '%,%')
UNION (SELECT col1, SPLIT_STR(col2, ',', 3) c2 from tbl where col2 like '%,%,%')
...

I'm not very up on mysql stored procedures, but perhaps you could do some clever stuff with loops in order to make this more dynamic.

I hope this points you in the right direction - ?

---

I really wanted to get this to work using SQL but was unable to. I wrote this quick PHP script to do the job.

It's quick and dirty and NOT how I would ever recommend doing production code. But sometimes you do what it takes to get the job done quick.

<?php
$table = "ref_app";
$pri_column = "Repo";
$column = "Topics";
$newTable = "topics";

$conn = mysql_connect("localhost", "dev", "password");
mysql_select_db("esb_data");
if($conn==null){
    echo "Connection not made";
    exit;
}

$result = mysql_query("select ".$pri_column.", ".$column." from ".$table);
if(mysql_errno()){
    echo "<br>".mysql_error();
    exit;
}

$applications = array();
while($row = mysql_fetch_array($result)){
    echo "<br>".$row[$pri_column];
    $topics = explode(",", $row[$column]);
    foreach($topics as $topic){
        $topic = trim($topic);
        $applications[$row[$pri_column]][$topic] = $topic;
    }
    echo "<br>".$row[$column];
}
echo "<pre>";
print_r($applications);
echo "</pre>";

foreach($applications as $app => $topics){
    foreach($topics as $topic){
        $query = "insert into ".$newTable." values ('', \"".$app."\", \"".$topic."\")";
        echo "<br>".$query;
        mysql_query($query);
        if(mysql_errno()){
            echo "<br>".mysql_error();
        }
    }
}
?>

---

Overview

• This is to add on to the answers that suggest using UNION

Context

• MySQL 5.7

Problem

• User xyz001epo wishes to convert a single-row delimited string into multiple rows

Screenshot

• The goal is to change BEFORE into AFTER

  

Solution

• if the maximum number of items per single-row delimited string is known in advance, this can be accomplished using a UNION query over the known number of elements
• in this example we ignore the first element as it is considered superfluous to the output result (e.g., you have a case where all delimited strings start with some throw-away token such as http:// or something similar)

Sample code

SELECT
  tta.*
FROM
(
    SELECT
      rowid                                                              as txtname
      ,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(fld005,'/'),'/',2),'/',-1)  as txtvalu
    FROM zzdemo_uu112pingasrcdata
UNION
    SELECT
      rowid                                                              as txtname
      ,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(fld005,'/'),'/',3),'/',-1)  as txtvalu
    FROM zzdemo_uu112pingasrcdata
UNION
    SELECT
      rowid                                                              as txtname
      ,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(fld005,'/'),'/',4),'/',-1)  as txtvalu
    FROM zzdemo_uu112pingasrcdata
UNION
    SELECT
      rowid                                                              as txtname
      ,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(fld005,'/'),'/',5),'/',-1)  as txtvalu
    FROM zzdemo_uu112pingasrcdata
UNION
    SELECT
      rowid                                                              as txtname
      ,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(fld005,'/'),'/',6),'/',-1)  as txtvalu
    FROM zzdemo_uu112pingasrcdata
UNION
    SELECT
      rowid                                                              as txtname
      ,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(fld005,'/'),'/',7),'/',-1)  as txtvalu
    FROM zzdemo_uu112pingasrcdata
UNION
    SELECT
      rowid                                                              as txtname
      ,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(fld005,'/'),'/',8),'/',-1)  as txtvalu
    FROM zzdemo_uu112pingasrcdata
UNION
    SELECT
      rowid                                                              as txtname
      ,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(fld005,'/'),'/',9),'/',-1)  as txtvalu
    FROM zzdemo_uu112pingasrcdata
UNION
    SELECT
      rowid                                                              as txtname
      ,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(fld005,'/'),'/',10),'/',-1) as txtvalu
    FROM zzdemo_uu112pingasrcdata
) as tta
WHERE 1
  AND (txtvalu is not null)
  AND (txtvalu <> '')
;;;

Complete example

• a complete example is available at the public lab space for this answer

Rationale

• for use when the transformation must be done inside MySQL instead of on the application layer
• for use when the specific user account does not have permissions to create new functions in MySQL for security or other reasons

Pitfalls

• this solution assumes a known maximum number of items per delimited string which is not always an option
• this solution requires a trailing delimiter at the end of the delimited string, or else it will not produce the desired result
• this approach is not aesthetically pleasing, it will hurt some eyes and it will hurt some feelings