Tags: MySQL , Stored Procedure

As name suggest SPLIT means separate out something. Here we are talking about separating character, words from given string by specifying some special character.

Note: MySQL has limitation that It can't return table from function. That's why we have to create stored procedure of SPLIT Function in order to get value in tabular format.

DROP PROCEDURE IF EXISTS SP_SPLIT;
DELIMITER $$
CREATE PROCEDURE `SP_SPLIT`(
 IN StringValue LONGTEXT,
 IN DelimiterChar  VARCHAR(1))
BEGIN
 DECLARE INDX INT;
 DECLARE SLICE nvarchar(4000); 
 SET INDX = 1;
 
 DROP TEMPORARY TABLE IF EXISTS Results;
 CREATE TEMPORARY TABLE Results(Items VARCHAR(100));

 MyLoop: WHILE INDX != 0 DO
 -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
 SET INDX = LOCATE(DelimiterChar,StringValue);
 -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
 IF INDX !=0 THEN
  SET SLICE = LEFT(StringValue,INDX - 1);
 ELSE
  SET SLICE = StringValue;
 END IF;
 -- PUT THE ITEM INTO THE RESULTS SET
 INSERT INTO Results(Items) VALUES(SLICE);
 -- CHOP THE ITEM REMOVED OFF THE MAIN STRING
 SET StringValue = RIGHT(StringValue,LENGTH(StringValue) - INDX);
 -- BREAK OUT IF WE ARE DONE
 IF LENGTH(StringValue) = 0 THEN
  LEAVE MyLoop;
 END IF;
 END WHILE;
END

Step 1: Execute above Stored Procedure in your MySQL.

Step 2: Call Stored Procedure and select result.

CALL SP_SPLIT("HELLO,WORLD,JAVAQUERY,THIS IS LONG STRING. YOU CAN DO EVEN BETTER",",");
/* Results is table created in SP_SPLIT stored procedure. */
SELECT * FROM RESULTS;

To process further on your result read "MySQL: How to select data from Stored Procedure in to Stored Procedure"

Tags: MySQL , Stored Procedure