MySQL Split Comma Separated String Into Temp Table

Question

This is pretty much the same question as Can Mysql Split a column?

MySQL doesn't have a split string function so you have to do work arounds. You can do anything with the data once you split it using one of the methods listed on the answer page above.

You can loop over that custom function and break when it returns empty, you'll have to play and learn some syntax (or at least I would) but the syntax for a FOR loop in mysql is here: http://www.roseindia.net/sql/mysql-example/for.shtml

You can iterate over it, incrementing the position in the function below:

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

(Credit: https://blog.fedecarg.com/2009/02/22/mysql-split-string-function/ )

Which should return '' if no match is found, so break the loop if no match is found. This will allow you to with only mysql parse over the split string and run the insert queries into a temp table. But man why not just use a scripting language like php for that kind of work? :(

Code for loop syntax:

DELIMITER $$  

CREATE PROCEDURE ABC(fullstr)

   BEGIN
      DECLARE a INT Default 0 ;
      DECLARE str VARCHAR(255);
      simple_loop: LOOP
         SET a=a+1;
         SET str=SPLIT_STR(fullstr,"|",a);
         IF str='' THEN
            LEAVE simple_loop;
         END IF;
         #Do Inserts into temp table here with str going into the row
         insert into my_temp_table values (str);
   END LOOP simple_loop;
END $$

I found good solution for this

https://forums.mysql.com/read.php?10,635524,635529

Thanks to Peter Brawley

Trick: massage a Group_Concat() result on the csv string into an Insert...Values... string:

drop table if exists t;
create table t( txt text );
insert into t values('1,2,3,4,5,6,7,8,9');

drop temporary table if exists temp;
create temporary table temp( val char(255) );
set @sql = concat("insert into temp (val) values ('", replace(( select group_concat(distinct txt) as data from t), ",", "'),('"),"');");
prepare stmt1 from @sql;
execute stmt1;
select distinct(val) from temp;
+------+
| val  |
+------+
| 1    |
| 2    |
| 3    |
| 4    |
| 5    |
| 6    |
| 7    |
| 8    |
| 9    |
+------+

Also if you just want to join some table to list of id you can use LIKE operator. There is my solution where I get list of id from blog post urls, convert them to comma separated list started and finished with commas and then join related products by id list with LIKE operator.

SELECT b2.id blog_id, b2.id_list, p.id
FROM (
    SELECT b.id,b.text,
    CONCAT(
        ",",
            REPLACE(
                EXTRACTVALUE(b.text,'//a/@id')
                , " ", ","
            )
        ,","
    ) AS id_list
    FROM blog b
) b2
LEFT JOIN production p ON b2.id_list LIKE CONCAT('%,',p.id,',%')
HAVING b2.id_list != ''

DELIMITER $$  

CREATE PROCEDURE SPLIT_VALUE_STRING()

    BEGIN

        SET @String      = '1,22,333,444,5555,66666,777777';
        SET @Occurrences = LENGTH(@String) - LENGTH(REPLACE(@String, ',', ''));
        myloop: WHILE (@Occurrences > 0)
        DO 
            SET @myValue = SUBSTRING_INDEX(@String, ',', 1);
            IF (@myValue != '') THEN
            /* my code... */
            ELSE
                LEAVE myloop; 
            END IF;
            SET @Occurrences = LENGTH(@String) - LENGTH(REPLACE(@String, ',', ''));
            IF (@occurrences = 0) THEN 
                LEAVE myloop; 
            END IF;
            SET @String = SUBSTRING(@String,LENGTH(SUBSTRING_INDEX(@String, ',', 1))+2);
        END WHILE;                  

   END $$

I have done this, for when you don't have table values and so on:

select *
from(
    select c, SUBSTRING_INDEX(SUBSTRING_INDEX('1|2|5|6', '|', c+1), '|', -1) as name
    from(
        SELECT (TWO_1.SeqValue + TWO_2.SeqValue + TWO_4.SeqValue + TWO_8.SeqValue + TWO_16.SeqValue + TWO_32.SeqValue) c
        FROM (
            SELECT 0 SeqValue UNION ALL SELECT 1 SeqValue) TWO_1
            CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 2 SeqValue) TWO_2
            CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 4 SeqValue) TWO_4
            CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 8 SeqValue) TWO_8
            CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 16 SeqValue) TWO_16 
            CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 32 SeqValue) TWO_32
    ) as b
    WHERE c <= (CHAR_LENGTH('1|2|5|6') - CHAR_LENGTH(REPLACE('1|2|5|6', '|', '')))
) as a;

May not be the best answer, but works without aid of functions and procedures, no additional tables etc.

select distinct
  SUBSTRING_INDEX(SUBSTRING_INDEX('1,2,3,4', ',', numbers.n), ',', -1) name
from
  (select @rownum := @rownum + 1 as n
from YourTable
cross join (select @rownum := 0) r
) numbers 
order by
   n

You can use regular expression in MySQL to specify a pattern for a complex search, you cannot parse the strings.

But you can build INSERT query with the help of REPLACE and CONCATENATE to save data to temp table.