MySQL: split string

Posted March 11, 2009 * Comments(2)

There is no built-in function to split comma-separated string in MySQL. However it is possible to google lots of different ways to do that.

Here it is how it was done in my case when comma-separated list of integer IDs had been provided to be processed inside another stored procedure. But it should be pretty simple to adjust this procedure if comma-separated values are not numbers but for example words.

CREATE PROCEDURE String_Split
(
  vString VARCHAR(8000),
  vSeparator VARCHAR(5)
)
BEGIN
 
DECLARE vDone tinyint(1) DEFAULT 1;
DECLARE vIndex INT DEFAULT 1;
DECLARE vSubString VARCHAR(15);
 
DROP TABLE IF EXISTS tmpIDList;
CREATE TEMPORARY TABLE tmpIDList (ID INT);
 
WHILE vDone > 0 DO
  SET vSubString = SUBSTRING(vString, vIndex,
                    IF(LOCATE(vSeparator, vString, vIndex) > 0,
                      LOCATE(vSeparator, vString, vIndex) - vIndex,
                      LENGTH(vString)
                    ));
  IF LENGTH(vSubString) > 0 THEN
      SET vIndex = vIndex + LENGTH(vSubString) + 1;
      INSERT INTO tmpIDList VALUES (vSubString);
  ELSE
      SET vDone = 0;
  END IF;
END WHILE;
 
END;

So table tmpIDList contains all values and can be used in any SQL query later (and then dropped).

2 Comments so far

  1. suraj on October 7th, 2009

    Hello, nice sp. Exactly wat i was lookin for! But you have a missing closing bracket after
    ‘IF(LOCATE(vSeparator, …)’

  2. Alexander on October 7th, 2009

    Thanks for your comment!

Leave a reply