Pfthb

I have table :

id | name 
1 | a,b,c 
2 | b

i want output like this :

id | name 
1 | a 
1 | b 
1 | c 
2 | b

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.

I have take the reference from here with changed column name.

DELIMITER $$

CREATE FUNCTION strSplit(x VARCHAR(65000), delim VARCHAR(12), pos INTEGER) 
RETURNS VARCHAR(65000)
BEGIN
 DECLARE output VARCHAR(65000);
 SET output = REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos)
 , LENGTH(SUBSTRING_INDEX(x, delim, pos - 1)) + 1)
 , delim
 , '');
 IF output = '' THEN SET output = null; END IF;
 RETURN output;
END $$


CREATE PROCEDURE BadTableToGoodTable()
BEGIN
 DECLARE i INTEGER;

 SET i = 1;
 REPEAT
 INSERT INTO GoodTable (id, name)
 SELECT id, strSplit(name, ',', i) FROM BadTable
 WHERE strSplit(name, ',', i) IS NOT NULL;
 SET i = i + 1;
 UNTIL ROW_COUNT() = 0
 END REPEAT;
END $$

DELIMITER ;

My variant: stored procedure that takes table name, field names and delimiter as arguments. Inspired by post http://www.marcogoncalves.com/2011/03/mysql-split-column-string-into-rows/

delimiter $$

DROP PROCEDURE IF EXISTS split_value_into_multiple_rows $$
CREATE PROCEDURE split_value_into_multiple_rows(tablename VARCHAR(20),
 id_column VARCHAR(20), value_column VARCHAR(20), delim CHAR(1))
 BEGIN
 DECLARE id INT DEFAULT 0;
 DECLARE value VARCHAR(255);
 DECLARE occurrences INT DEFAULT 0;
 DECLARE i INT DEFAULT 0;
 DECLARE splitted_value VARCHAR(255);
 DECLARE done INT DEFAULT 0;
 DECLARE cur CURSOR FOR SELECT tmp_table1.id, tmp_table1.value FROM 
 tmp_table1 WHERE tmp_table1.value IS NOT NULL AND tmp_table1.value != '';
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

 SET @expr = CONCAT('CREATE TEMPORARY TABLE tmp_table1 (id INT NOT NULL, value VARCHAR(255)) ENGINE=Memory SELECT ',
 id_column,' id, ', value_column,' value FROM ',tablename);
 PREPARE stmt FROM @expr;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;

 DROP TEMPORARY TABLE IF EXISTS tmp_table2;
 CREATE TEMPORARY TABLE tmp_table2 (id INT NOT NULL, value VARCHAR(255) NOT NULL) ENGINE=Memory;

 OPEN cur;
 read_loop: LOOP
 FETCH cur INTO id, value;
 IF done THEN
 LEAVE read_loop;
 END IF;

 SET occurrences = (SELECT CHAR_LENGTH(value) -
 CHAR_LENGTH(REPLACE(value, delim, '')) + 1);
 SET i=1;
 WHILE i <= occurrences DO
 SET splitted_value = (SELECT TRIM(SUBSTRING_INDEX(
 SUBSTRING_INDEX(value, delim, i), delim, -1)));
 INSERT INTO tmp_table2 VALUES (id, splitted_value);
 SET i = i + 1;
 END WHILE;
 END LOOP;

 SELECT * FROM tmp_table2;
 CLOSE cur;
 DROP TEMPORARY TABLE tmp_table1;
 END; $$

delimiter ;

Usage example (normalization):

CALL split_value_into_multiple_rows('my_contacts', 'contact_id', 'interests', ',');

CREATE TABLE interests (
 interest_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 interest VARCHAR(30) NOT NULL
) SELECT DISTINCT value interest FROM tmp_table2;

CREATE TABLE contact_interest (
 contact_id INT NOT NULL,
 interest_id INT NOT NULL,
 CONSTRAINT fk_contact_interest_my_contacts_contact_id FOREIGN KEY (contact_id) REFERENCES my_contacts (contact_id),
 CONSTRAINT fk_contact_interest_interests_interest_id FOREIGN KEY (interest_id) REFERENCES interests (interest_id)
) SELECT my_contacts.contact_id, interests.interest_id
 FROM my_contacts, tmp_table2, interests
 WHERE my_contacts.contact_id = tmp_table2.id AND interests.interest = tmp_table2.value;

CREATE PROCEDURE `getVal`()
BEGIN
 declare r_len integer;
 declare r_id integer;
 declare r_val varchar(20);
 declare i integer;
 DECLARE found_row int(10);
 DECLARE row CURSOR FOR select length(replace(val,"|","")),id,val from split;
 create table x(id int,name varchar(20));
 open row;
 select FOUND_ROWS() into found_row ;
 read_loop: LOOP
 IF found_row = 0 THEN
 LEAVE read_loop;
 END IF;
 set i = 1; 
 FETCH row INTO r_len,r_id,r_val;
 label1: LOOP 
 IF i <= r_len THEN
 insert into x values( r_id,SUBSTRING(replace(r_val,"|",""),i,1));
 SET i = i + 1;
 ITERATE label1;
 END IF;
 LEAVE label1;
 END LOOP label1;
 set found_row = found_row - 1;
 END LOOP;
 close row;
 select * from x;
 drop table x;
END

Here is my solution

-- Create the maximum number of words we want to pick (indexes in n)
with recursive n(i) as (
 select
 1 i
 union all
 select i+1 from n where i < 1000
)
select distinct
 s.id,
 s.oaddress,
 -- n.i,
 -- use the index to pick the nth word, the last words will always repeat. Remove the duplicates with distinct
 if(instr(reverse(trim(substring_index(s.oaddress,' ',n.i))),' ') > 0,
 reverse(substr(reverse(trim(substring_index(s.oaddress,' ',n.i))),1,
 instr(reverse(trim(substring_index(s.oaddress,' ',n.i))),' '))),
 trim(substring_index(s.oaddress,' ',n.i))) oth
from 
 app_schools s,
 n