String Split function in MySQL

Question

can anybody tell me how to implement a split function in mysql which behaves like Javascript split.

I want a function like this

SELECT Split('a,b,c,d', ',') AS splitted

Which give result like

splitted
--------------
a 
b 
c 
d 

Can anybody help me?

I saw some answers here, and somewhere else, but those functions need the position of string to be returned, which I don't want/have

Thank you

Answer 1

It is possible:

-- Preparation
CREATE TABLE tb_Digits (d int(11) UNSIGNED NOT NULL PRIMARY KEY);
INSERT tb_Digits VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
INSERT IGNORE tb_Digits
SELECT o1.d+o2.d*10+o3.d*100+o4.d*1000 d FROM tb_Digits o4, tb_Digits o3, tb_Digits o2, tb_Digits o1;

CREATE PROCEDURE pc_splitStr(IN in_string TEXT, IN in_separator CHAR(1))
COMMENT 'Use (SELECT word FROM return_splitStr) para ver o resultado'
BEGIN
  DECLARE o_limit INT(11) UNSIGNED DEFAULT LENGTH(in_string)-LENGTH(REPLACE(in_string,in_separator,''))+1;
  DROP TABLE IF EXISTS return_splitStr;
  CREATE TEMPORARY TABLE return_splitStr (word TEXT);
  INSERT return_splitStr
  SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(in_string, in_separator, d.d), in_separator, -1)
  FROM tb_Digits d
  WHERE d.d>0 AND d.d<=o_limit;
END;

-- Execution
CALL pc_splitStr('a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z',',');
SELECT word FROM return_splitStr;

Answer 2

You can create a function and return the specific result you are waiting for.

here is an example with multi ids in one column extracted and queried to get the specific values :

DROP FUNCTION IF EXISTS getValuesByOptionIds;

DELIMITER $$
CREATE FUNCTION getValuesByOptionIds
(
   optionIds VARCHAR(255)
   )
   RETURNS VARCHAR(255)
   DETERMINISTIC
BEGIN
  DECLARE iter INTEGER DEFAULT 0;
  DECLARE result VARCHAR(255) DEFAULT '';
  DECLARE previousIter VARCHAR(255) DEFAULT '';

  iters: WHILE LENGTH(SUBSTRING_INDEX(optionIds, ',', iter + 1)) != LENGTH(previousIter) DO
  SET iter = iter + 1;
  SET previousIter = SUBSTRING_INDEX(optionIds, ',', iter);

  IF iter = 1 THEN
    SET result = (select tmp.value from eav_attribute_option_value tmp where tmp.option_id = SUBSTRING_INDEX(optionIds, ',', iter));
  ELSEIF iter > 1 THEN
    SET result = concat(result, ',', (select tmp.value from eav_attribute_option_value tmp where tmp.option_id = SUBSTRING_INDEX(SUBSTRING_INDEX(optionIds, ',', iter), ',', -1)));
  END IF;
  END WHILE iters;

  RETURN result;
END$$
DELIMITER ;