MySql: Stored Procedure for split, return table

Question

I found a lot of examples of split-procedures for MySql returning the xth-part of a string. But I need a split-procedure to return ALL parts of a splitted string, so someling like:

SELECT split(",", "1,2,3,4,5,6");

Should return;

+-------+
| split |
+-------+
|     1 |
|     2 |
|     3 |
|     4 |
|     5 |
|     6 |
+-------+

I tried:

DELIMITER $$

CREATE PROCEDURE `split`(delimeter VARCHAR(1),txt VARCHAR(65535))
RETURNS split TABLE (
  part varchar(1024) NOT NULL)
DETERMINISTIC
BEGIN

DECLARE pos, posOld;

set pos := locate(delimeter,txt);
set posOld = 1;

WHILE pos > 0 DO
 set part := subst(text, posOld, pos-1);
 set posOld := pos+1;
 insert into split (`part`) values (part);

 set pos := locate(delimeter, txt, posOld);
END WHILE;

END$$

but get the error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'split TABLE (part varchar(1024) NOT NULL)

Whats the correct way to do it?

You are mixing up procedures and functions - procedures do not have RETURNS and mysql does not support table valued functions. — P.Salmon
– P.Salmon, Commented Apr 10, 2018 at 13:55
That will never work like you would want it to work.. stored procedure can not be called with a SELECT you need to use CALL ... a function can be made that work on select but can't return multiple records. — Raymond Nijland
– Raymond Nijland, Commented Apr 10, 2018 at 14:13

Raymond Nijland · Accepted Answer · 2018-04-10 14:35:51Z

You can do this with plain SQL only.
This trick is by using a MySQL number generator in combination with nested SUBSTRING_INDEX functions.

Query

This query will generate numbers from 1 to 100.
So the final query can support up to 100 separated values.

SELECT 
 (@number  := @number + 1) AS number
FROM (
  SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) AS record_1
CROSS JOIN (
  SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) AS record_2
CROSS JOIN ( SELECT @number := 0 ) AS init_user_param

see demo http://sqlfiddle.com/#!9/c314ca/5

Now we are going to extract the values from the comma separated string with

Query

Replace [position] with a number from 0 - 5 which value you want to extract from the comma separated string.

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('1,2,3,4,5', ',', [position]), ',', -1) AS split;

see demo http://sqlfiddle.com/#!9/c314ca/16

Now we know the basics we are going to combine both queries to a working solution.

Query

SELECT
  DISTINCT 
    SUBSTRING_INDEX(SUBSTRING_INDEX(@CSV, ',', generator.number), ',', -1) AS split
FROM (

  SELECT 
   (@number  := @number + 1) AS number
  FROM (
    SELECT 0 UNION SELECT 1 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) AS record_1
  CROSS JOIN (
    SELECT 0 UNION SELECT 1 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) AS record_2
  CROSS JOIN ( SELECT @number := 0 ) AS init_user_param
) 
 AS generator
CROSS JOIN (
  SELECT @CSV := '1,2,3,4,5'
) AS init_user_param

see demo http://sqlfiddle.com/#!9/c314ca/20

Collectives™ on Stack Overflow

MySql: Stored Procedure for split, return table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related