MySQL Iterate through elements of a split string and do things with them

Question

I have a cell in one of my MySQL community edition 5.1 tables. The contents are always certain number(s). If there is more than one number, then it is delimited by an ; semi-colon.

For example:

| Column |
1ar8fj
99dkek;adjidk3;dajdid
divdae;dadjid;
NULL
dkadjcud;dasd;adfkvdo
dkjfakj
...

I need make some code that takes each column value, splits it up by the ; and then uses each value after it was split up to do another query, and output the results.

I know I can do this with PHP but I don't need to make this into a webpage, so I was wondering if this is possible write in MySQL syntax? The PHP code would look something like this:

<?php
    $result = $mysqli->query('select column from table;');
    while ($row = $result->fetch_array($result)){
        $id_numbers = explode($row[0],';');
        foreach($id_numbers as $key => $val){
            // do another query
            $result2 = $mysqli->query('select * from table2 where col_val = "'.$val.'"');
            while ($row2 = $result2->fetch_array($result2){
                echo $row2[0].'<br>';
            }
        }
    }
?>

Is this possible directly in MySQL syntax?

Thanks!!!

true dat this is just some pseudo code, i do not intend to use this code whatsoever, just give everyone an idea of what I am trying to accomplish in MySQL — jeffery_the_wind, Jul 27 '12 at 16:34
Please, don't use `mysql_*` functions to write new code. They are no longer maintained and the community has begun [deprecation process](http://goo.gl/KJveJ). See the *[red box](http://goo.gl/GPmFd)*? Instead you should learn about [prepared statements](http://goo.gl/vn8zQ) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you can't decide which, [this article](http://goo.gl/3gqF9) will help you. If you pick PDO, [here is good tutorial](http://goo.gl/vFWnC). — Madara's Ghost, Jul 27 '12 at 16:35
You are focusing on the un-important aspects of this question — jeffery_the_wind, Jul 27 '12 at 16:37
Have a look at [this question](http://stackoverflow.com/questions/1928274/mysql-stored-procedure-for-splitting-strings-by-delimiter) on how to split a string by a delimiter in mysql — Aleks G, Jul 27 '12 at 16:44
I believe you could declare a temp table, pass the string to a stored procedure that uses a function to recursively split the string and outputs to the temp table, and then you can select the contents of the temp table for display, but this is me just thinking theoretically. I'd have to try it before I gave a definite answer. — Ally, Jul 27 '12 at 16:52
Also, when you output the results are you wanting to store them somewhere with a relation back to their origin? — Ally, Jul 27 '12 at 16:56
Take a look at that string splitter from @AleksG and also [this](http://stackoverflow.com/questions/221194/performance-of-recursive-stored-procedures-in-mysql-to-get-hierarchical-data) recursive stored procedure question/answer that talks about temp tables. — Ally, Jul 27 '12 at 16:59

score 2 · Accepted Answer · answered Jul 27 '12 at 20:29

PHEW. Okay. I finally got it working, but here's a solution as a stored procedure that takes a string as an input for the delimiter and is runs on the given table called testtable

--Procedure: sprecursplit

--DROP PROCEDURE IF EXISTS sprecursplit;

DELIMITER |

CREATE PROCEDURE sprecursplit 
(
  IN delim nvarchar(255)
)
BEGIN
  declare tdone tinyint unsigned default(0);
  declare depth int unsigned default(1);
  declare datas nvarchar(255) default('');
  declare done tinyint unsigned default(0);
  declare dlength int unsigned default(1);
  declare hlength int unsigned default(0);
  declare pos int unsigned default(1);
  declare runpos int unsigned default(1);
  declare slice nvarchar(255) default('');

  drop table if exists allsubs;
  create temporary table allsubs(id int unsigned auto_increment, val nvarchar(255), primary key (id))engine = memory;

  while tdone <> 1 do
        if depth <= (select count(*) from testtable) then
           select t.datastring into datas from testtable t where t.id = depth limit 1;
           if length(datas) > 0 then
                set dlength = length(delim);
                set hlength = length(datas);
                set pos = 1;
                set runpos = 1;
                set slice = '';
                set done = 0;
                if hlength > 0 then
                   while done <> 1 do
                         if runpos > hlength then
                            set done = 1;
                         else
                             set pos = locate(delim, substring(datas from runpos));
                             if pos <> 1 then
                                if pos > 1 then
                                   set slice = substring(datas from runpos for (pos - 1));
                                else
                                    set slice = substring(datas from runpos);    
                                end if;
                                insert into allsubs (val) values (slice);
                             end if;
                             if pos = 0 then
                                 set runpos = runpos + hlength;
                             else
                                 set runpos = runpos + pos + dlength - 1;
                             end if;
                         end if;
                   end while;
                end if;
           end if;
           set depth = depth + 1;
        else
            set tdone = 1;
        end if;
  end while;
  select * from allsubs;
  drop table allsubs;
END|

DELIMITER ;

While this is impressive, I'd hate to come across this gem in any application I was tasked with maintaining. — tadman, Jul 27 '12 at 20:45

MySQL Iterate through elements of a split string and do things with them

1 Answers1

Linked