How to convert comma seperated values into table-rows

Let's say we've set of data in array format e.g. "Ram, Shyam, Mohan, Sohan, Sohit"
and we want a result-set in tabular format

Name

Ram

Shyam

Mohan

Sohan

Sohit

Mysql database doesn't support array data-type, so we can't do the conversion easily in the expected format.
Here is the query to full-fill this requirement.

Step1. Execute the below query in your MySQL database schema

-- STORED PROCEDURES

DROP PROCEDURE IF EXISTS ;

split_words_into_rows

DELIMITER #

CREATE PROCEDURE split_words_into_rows(IN _in_tags_to_be_seperated VARCHAR(8000), IN _in_seperator CHAR(3))

proc_main:BEGIN -- 1. Begin Proc

-- watch out for variable names that have the same names as fields !!

DECLARE v_keyword VARCHAR(255);

DECLARE v_keyword_id MEDIUMINT UNSIGNED;

-- Iteration variable (v_tags_done: if done through each and every word of input string paramter,

--                                    v_tags_idx: currently running id )                                                     

DECLARE v_tags_done TINYINT UNSIGNED;

DECLARE v_tags_idx INT UNSIGNED;

-- Temporary Table Declaration & initialization (as DB doesn't support array, we've to split the incoming tag based and put each and

--                             every "tag" into temporary table, MAKE SURE TO DROP IT BEFORE LEAVING THE PROC)

-- drop temporary table

DROP TEMPORARY TABLE IF EXISTS temp_words_to_delete;

--  create temporary table

CREATE TEMPORARY TABLE temp_words_to_delete (Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, Word VARCHAR(256));

-- set autocommit false. we'll commit manually.

SET autocommit = 0;

  -- split the out the _in_tags_to_be_seperated and insert into temporary table

  SET v_tags_done = 0;      

  SET v_tags_idx = 1;

  WHILE NOT v_tags_done DO -- 1.BEGIN WHILE 

    -- get the word from "," to ","

    SET v_keyword = SUBSTRING(_in_tags_to_be_seperated, v_tags_idx,

      IF(LOCATE(_in_seperator, _in_tags_to_be_seperated, v_tags_idx) > 0,

        LOCATE(_in_seperator, _in_tags_to_be_seperated, v_tags_idx) - v_tags_idx,

        LENGTH(_in_tags_to_be_seperated))

        );

      IF LENGTH(v_keyword) > 0 THEN

        SET v_tags_idx = v_tags_idx + LENGTH(v_keyword) + 1;

        SET v_keyword = TRIM(v_keyword);

        -- add the keyword into temporary table

        INSERT IGNORE INTO temp_words_to_delete (Word) VALUES (v_keyword);

      ELSE

        SET v_tags_done = 1;

      END IF;

  END WHILE; -- 1.END WHILE 

COMMIT;

-- Select records

SELECT Id, Word FROM temp_words_to_delete;

-- Clean your DB: drop temporary table (it should be last statement

--                                      , if you want to use the data of temporary table in between the proc)

DROP TEMPORARY TABLE IF EXISTS temp_words_to_delete;

END proc_main # -- -- 1. End Proc

 Step2. Use following query to verify the result set.

CALL split_words_into_rows('Ram, Shyam, Mohan, Sohan, Sohit', ',');

You'll get output like this