split a comma spearated string to a table in mysql

Question

I have made similar function like http://ole.michelsen.dk/blog/split-string-to-table-using-transact-sql.html in sql server where i send csv string like "1,2,3,4,5" and it acted like table. Can there be some similar thing in mysql

I have to pass "1,2,3,4,5" as Text or varchar(1000)

Select Id from CSVToTable(merchantsIds) 
    where id not in 
    (
        SELECT DISTINCT merchantid 
        FROM `transaction`
        WHERE `statusid` =_status            
    )

remember my merchantid field is of int type

http://stackoverflow.com/questions/11835155/mysql-split-comma-separated-string-into-temp-table — David Soussan, Mar 20 '15 at 08:24

score 1 · Answer 1 · answered Mar 20 '15 at 08:27

try this stored procedure...

After creating this sproc you can call it like this

call split_tags_into_rows('ram, shyam, mohan, 1, 2012-01-03', ',');

-- STORED PROCEDURES

DROP PROCEDURE IF EXISTS split_tags_into_rows;

DELIMITER #

CREATE PROCEDURE split_tags_into_rows(IN _in_tags_to_be_seperated VARCHAR(8000), IN _in_seperator CHAR(3))
proc_main:BEGIN -- 1. Begin Proc

DECLARE v_keyword VARCHAR(255);
DECLARE v_keyword_id MEDIUMINT UNSIGNED;

-- Iteration variable (v_tags_done: if done through each and every work 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_token_to_delete;

--  create temporary table
CREATE TEMPORARY TABLE temp_token_to_delete (Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, Word VARCHAR(256));

-- 
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_token_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_token_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_token_to_delete;

END proc_main # -- -- 1. End Proc

Thanks Raushan but no temp table – Kamran Shahid Mar 20 '15 at 09:28 — Kamran Shahid, Mar 20 '15 at 09:28

split a comma spearated string to a table in mysql

1 Answers1