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