Search code examples
mysqlsplitsubstringtrim

How to split a result from select column in mySQL to a mutiple columns

I have column in MYSQL database would like to extract that column data and split it into multiple columns Here is a sample of data that I would like to split

```

{"1744":"1","1745":"1","1747":"1","1748":"1","1749":"1","1750":"1"}
{"1759":"1"}
{"47":"1","48":"Ehebr","49":"1479977045596.jpg"}

``` I would like to split that into two columns like so with the first data: The desired out come

as you notice this data come in different lengths and would like to be able to split any length of data, had a look here [How to split a resulting column in multiple columns but I don't think that is what i want the result I got there was like soresult from the example would also like to trim all the other braces and quotes on the data. here is my code so far ```

SELECT combined,SUBSTRING_INDEX( combined , ':', 1 ) AS a,
SUBSTRING_INDEX(SUBSTRING_INDEX( combined , ':', 2 ),':',-1) AS b,
 SUBSTRING_INDEX(SUBSTRING_INDEX( combined , ':', -2 ),':',1) AS c,
 SUBSTRING_INDEX( combined , ':', -1 ) AS d
FROM tablefoo WHERE combined is not null;

```

Solution

  • If you can live with procedures and cursors

    drop procedure if exists p;
delimiter //
CREATE DEFINER=`root`@`localhost` PROCEDURE `p`(
    IN `instring` varchar(255)

)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
begin
declare   tempstring varchar(10000);
declare   outstring  varchar(100);
declare   c1 varchar(100);
declare   c2 varchar(100);
declare  checkit int;
declare done int;
DECLARE CUR1 CURSOR for SELECT t.col  FROM T;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

drop table if exists occursresults;
create table occursresults (col1 varchar(20), col2 varchar(20));

open CUR1;
read_loop: LOOP
        FETCH CUR1  INTO tempstring;

        if done then leave read_loop; end if;

        set tempstring = replace(tempstring,'{','');
        set tempstring = replace(tempstring,'}','');
        set tempstring = replace(tempstring,'"','');
        set checkit = 0;
        #select tempstring;

        looper: while   tempstring is not null and instr(tempstring,',') > 0 do
                set checkit = checkit + 1;
                if checkit > 100 then #In case of infinite loop
                    leave looper;
                end if;
                set outstring = substr(tempstring,1,instr(tempstring, ',') - 1);
                set tempstring = ltrim(rtrim(replace(tempstring,concat(outstring,','),'')));
                set c1 = substr(outstring,1,instr(outstring, ':') - 1);
                set c2 = replace(outstring,concat(c1,':'),'');
                INSERT INTO OCCURSRESULTS (COL1,COL2) VALUES (c1,c2);
        #       select tempstring,outstring,c1,c2;      
        end while;
        #select tempstring;
        set outstring = tempstring; 
        set c1 = substr(outstring,1,instr(outstring, ':') - 1);
        set c2 = replace(outstring,concat(c1,':'),'');
        INSERT INTO OCCURSRESULTS (Col1,Col2) VALUES (c1,c2);


end loop;
close cur1;

end //

delimiter ;

MariaDB [sandbox]> select * from t;
+---------------------------------------------------------------------+
| col                                                                 |
+---------------------------------------------------------------------+
| {"1744":"1","1745":"1","1747":"1","1748":"1","1749":"1","1750":"1"} |
| {"1759":"1"}                                                        |
| {"47":"1","48":"Ehebr","49":"1479977045596.jpg"}                    |
+---------------------------------------------------------------------+
3 rows in set (0.00 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> call p(1);
Query OK, 0 rows affected (0.65 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> SELECT * FROM OCCURSRESULTS;
+------+-------------------+
| col1 | col2              |
+------+-------------------+
| 1744 | 1                 |
| 1745 | 1                 |
| 1747 | 1                 |
| 1748 | 1                 |
| 1749 | 1                 |
| 1750 | 1                 |
| 1759 | 1                 |
| 47   | 1                 |
| 48   | Ehebr             |
| 49   | 1479977045596.jpg |
+------+-------------------+
10 rows in set (0.00 sec)