php - 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!!!

Answer 1

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 ;