Converting Delimited String to Multiple Values in MySQL

Converting delimited string to multiple values in mysql

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.

SQL split values to multiple rows

If you can create a numbers table, that contains numbers from 1 to the maximum fields to split, you could use a solution like this:

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
  numbers inner join tablename
  on CHAR_LENGTH(tablename.name)
     -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
  id, n

Please see fiddle here.

If you cannot create a table, then a solution can be this:

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
  (select 1 n union all
   select 2 union all select 3 union all
   select 4 union all select 5) numbers INNER JOIN tablename
  on CHAR_LENGTH(tablename.name)
     -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
  id, n

an example fiddle is here.

Mysql: insert multiple values with order number

You probably can do something like this:

WITH RECURSIVE cte AS (
SELECT 1 idx, LENGTH(REGEXP_REPLACE(stringVal,'[^,]',''))+1 AS maxLen 
  FROM mytable UNION
SELECT idx+1, maxLen FROM cte WHERE idx+1 <= maxLen)

SELECT idx,
       SUBSTRING_INDEX(SUBSTRING_INDEX(stringVal,',',idx),',',-1) AS val
  FROM cte
CROSS JOIN mytable;

Generating idx using cte based on how many strings were separated by comma in the column. The steps are:

1. Use REGEXP_REPLACE() to replace any value that is not comma; returns ,,.
2. Use LENGTH() on the regexp to get the total of comma; returns 2.
3. Add 1 (+1) to the length result returned; final value 3.
4. The cte result are:

    +-----+--------+
    | idx | maxLen |
    +-----+--------+
    |  1  |   3    |
    |  2  |   3    |
    |  3  |   3    |
    +-----+--------+

From the generated cte , we're going to use idx value with SUBSTRING_INDEX() to separate the comma-separated, single-row string into multiple rows.

Understanding what the query is doing:

This, we can emulate with 3 of the same query repeated UNION ALL together like the following:

SELECT 1 AS 'index',
       SUBSTRING_INDEX(SUBSTRING_INDEX(stringVal,',',1),',',-1) AS val
  FROM mytable
UNION ALL
SELECT 2,
       SUBSTRING_INDEX(SUBSTRING_INDEX(stringVal,',',2),',',-1)
  FROM mytable
UNION ALL
SELECT 3,
       SUBSTRING_INDEX(SUBSTRING_INDEX(stringVal,',',3),',',-1)
  FROM mytable;

Which also can be done as such; Same idea as the cte approach:

SELECT idx,
       SUBSTRING_INDEX(SUBSTRING_INDEX(stringVal,',',idx),',',-1) AS val
  FROM (SELECT 1 idx UNION
        SELECT 2 UNION
        SELECT 3 ) i
CROSS JOIN mytable;

Both of these query return the same result as the cte approach however it's not an ideal one; since we have to manually check how many string separated by comma and define (hard-code) it in the query. Imagine if a single row of string consists of something like a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, z - all the 26 alphabet; then that means the UNION query need to be repeated 26 times.

Demo fiddle

Split value into multiple rows

You are on the right track with the table of numbers. You should start by adding more rows, so it matches (or exceeds) the maximum possible number of elements in a CSV list.

Then, you can use a join condition to generate the relevant number of rows per name only

select
    t.*,
    substring_index(substring_index(t.name, ';', n.n), ';', -1) name
from numbers n
inner join testLocation t   
    on n <= length(t.name) - length(replace(t.name, ';', '')) + 1

Demo on DB Fiddle (I expanded the numbers to 8):

id | State   | name                                    | name        
-: | :------ | :-------------------------------------- | :-----------
 1 | Alabama | Birmingham;Huntsville;Mobile;Montgomery | Birmingham  
 1 | Alabama | Birmingham;Huntsville;Mobile;Montgomery | Huntsville  
 1 | Alabama | Birmingham;Huntsville;Mobile;Montgomery | Mobile      
 1 | Alabama | Birmingham;Huntsville;Mobile;Montgomery | Montgomery  
 2 | Florida | Tampa;Jacksonville;Destin               | Tampa       
 2 | Florida | Tampa;Jacksonville;Destin               | Jacksonville
 2 | Florida | Tampa;Jacksonville;Destin               | Destin      

Note, that, as commented already by others, storing CSV lists in a database is bad practice and should almost always be avoided. Recommended related reading: Is storing a delimited list in a database column really that bad?

Take a multi-delimited string in MySQL and convert to multiple records

This seems like a pretty gross thing to do in SQL, but here's a proof-of-concept script.

If the output looks right to you, replace the SELECT with an appropriate INSERT statement and it should get you what you want.

delimiter ;;

drop procedure if exists load_crazy_stuff;;

create procedure load_crazy_stuff(in s longtext)
begin
   declare pos       int;
   declare record    longtext;
   declare leftpart  int;
   declare rightpart longtext;
   set s = concat(s,';');
   while length(s)>0 DO
     set pos       = instr(s,';');
     set record    = left(s,pos-1);
     set s         = substr(s,pos+1);
     set pos       = instr(record,'~');
     set leftpart  = left(record,pos-1);
     set rightpart = substr(record,pos+1);
     select leftpart, rightpart;
   end while;
end;;

call load_crazy_stuff('3~S|Red|Top;1~S|Blue|Top;20~XL|Green|Left');;

Split delimited string value into rows

It may not be as difficult as I initially thought.

This is a general approach:

1. Count number of occurrences of the delimiter length(val) - length(replace(val, '|', ''))
2. Loop a number of times, each time grab a new delimited value and insert the value to a second table.

Split comma separated string into rows in mysql

Use a subquery of arbitrary digits to split your string.Instead of vals you can use '1,2,3'.

SELECT
  DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(vals, ',', n.digit+1), ',', -1) val
FROM
  tt1
  INNER JOIN
  (SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3  UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) n
  ON LENGTH(REPLACE(vals, ',' , '')) <= LENGTH(vals)-n.digit;

See it working

MySQL: Split comma separated list into multiple rows

In MySQL this can be achieved as below

SELECT id, length FROM vehicles WHERE id IN ( 117, 148, 126) 

+---------------+
| id  | length  |
+---------------+
| 117 | 25      |
| 126 | 8       |
| 148 | 10      |
+---------------+

SELECT id,vehicle_ids FROM load_plan_configs WHERE load_plan_configs.id =42

+---------------------+
| id  | vehicle_ids   |
+---------------------+
| 42  | 117, 148, 126 |
+---------------------+

Now to get the length of comma separated vehicle_ids use below query

Output

SELECT length 
FROM   vehicles, load_plan_configs   
WHERE  load_plan_configs.id = 42 AND FIND_IN_SET(
       vehicles.id, load_plan_configs.vehicle_ids
)

+---------+
| length  |
+---------+
| 25      |
| 8       |
| 10      |
+---------+

For more info visit http://amitbrothers.blogspot.in/2014/03/mysql-split-comma-separated-list-into.html

---

Related Topics