2

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

Community
  • 1
  • 1
Harikrishnan
  • 3,664
  • 7
  • 48
  • 77
  • 1
    Functions can not return data set in MySQL. What you're describing is procedure output, but procedures can not be used in expressions (like within SELECT statements). Therefore, your two input points contradict each other and can not be fulfilled. – Alma Do Oct 22 '15 at 07:38
  • surely this is a duplicate – Jodrell Oct 22 '15 at 07:39
  • Oh ok. so what could be an alternative? – Harikrishnan Oct 22 '15 at 07:39
  • Possible duplicate of [How to turn a comma separated string into individual rows](http://stackoverflow.com/questions/33109814/how-to-turn-a-comma-separated-string-into-individual-rows) – Roman Marusyk Oct 22 '15 at 07:39
  • Possible Duplicated of http://stackoverflow.com/questions/2872358/sql-split-function – juankysmith Oct 22 '15 at 07:40
  • Possible duplicate of [Can you split/explode a field in a MySQL query?](http://stackoverflow.com/questions/471914/can-you-split-explode-a-field-in-a-mysql-query) – Jodrell Oct 22 '15 at 07:42
  • The alternative is: don't store sets of values as comma-separated strings, if you need to return them as sets of rows. There are [many other reasons](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574#3653574) to avoid using comma-separated strings in a relational database. – Bill Karwin Aug 08 '18 at 20:37

2 Answers2

3

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;
Helbert Fernandes
  • 46
  • 3
0

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 ;
Rami DH
  • 11
  • 1
  • 4