MySQL: split string

Posted March 11, 2009 * Comments(7)

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).

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

  3. Praveen on March 3rd, 2012

    Exactly what i was looking for.

    Your Coding Structure is Superb;

  4. Alexandre on July 23rd, 2013

    Hello, Beeing new at this, I’d like to know how to exploit the function now that I’ve stored the function in MySql ?

    I have a string type ( Item 1, Item 2, Item 3 ) that I’d like to use to SELECT rowns in my SLQ table. (w/ prepared req) Thx !

  5. Alex on December 2nd, 2013

    Alexandre,

    Once the stored procedure is created, it can be called as follows (for integers):

    CALL String_Split(‘1,2,3’, ‘,’);
    SELECT ID FROM tmpIDList;

    To use it with for types other than int you would need to modify create statement for the temporary table (i.e. CREATE TEMPORARY TABLE tmpIDList (ID INT)).

  6. Mohammad Gabr on May 23rd, 2014

    Although his post is old , but it fits what I need now.
    Thanks for it.

  7. Demini on February 24th, 2016

    thank you. Really helpfull

Leave a reply