How to Split Comma Separated Text in MySQL Stored Procedure

How to split comma separated text in MySQL stored procedure

This is simple as hell for MySQL:

SELECT * FROM table WHERE FIND_IN_SET(table.id, commaSeparatedData);

Reference: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

Split a string and loop through values in MySQL stored procedure

You'll need to be a little more careful with your string manipulation. You can't use REPLACE() for this, because that will replace multiple occurrences, corrupting your data if one element in the comma-separated list is a substring of another element. The INSERT() string function is better for this, not to be confused with the INSERT statement used for inserting into a table.

DELIMITER $$

DROP PROCEDURE IF EXISTS `insert_csv` $$
CREATE PROCEDURE `insert_csv`(_list MEDIUMTEXT)
BEGIN

DECLARE _next TEXT DEFAULT NULL;
DECLARE _nextlen INT DEFAULT NULL;
DECLARE _value TEXT DEFAULT NULL;

iterator:
LOOP
  -- exit the loop if the list seems empty or was null;
  -- this extra caution is necessary to avoid an endless loop in the proc.
  IF CHAR_LENGTH(TRIM(_list)) = 0 OR _list IS NULL THEN
    LEAVE iterator;
  END IF;
 
  -- capture the next value from the list
  SET _next = SUBSTRING_INDEX(_list,',',1);

  -- save the length of the captured value; we will need to remove this
  -- many characters + 1 from the beginning of the string 
  -- before the next iteration
  SET _nextlen = CHAR_LENGTH(_next);

  -- trim the value of leading and trailing spaces, in case of sloppy CSV strings
  SET _value = TRIM(_next);

  -- insert the extracted value into the target table
  INSERT INTO t1 (c1) VALUES (_value);

  -- rewrite the original string using the `INSERT()` string function,
  -- args are original string, start position, how many characters to remove, 
  -- and what to "insert" in their place (in this case, we "insert"
  -- an empty string, which removes _nextlen + 1 characters)
  SET _list = INSERT(_list,1,_nextlen + 1,'');
END LOOP;

END $$

DELIMITER ;

Next, a table for testing:

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The new table is empty.

mysql> SELECT * FROM t1;
Empty set (0.00 sec)

Call the procedure.

mysql> CALL insert_csv('foo,bar,buzz,fizz');
Query OK, 1 row affected (0.00 sec)

Note the "1 row affected" does not mean what you would expect. It refers to the last insert we did. Since we insert one row at a time, if the procedure inserts at least one row, you'll always get a row count of 1; if the procedure inserts nothing, you'll get 0 rows affected.

Did it work?

mysql> SELECT * FROM t1;
+----+------+
| id | c1   |
+----+------+
|  1 | foo  |
|  2 | bar  |
|  3 | buzz |
|  4 | fizz |
+----+------+
4 rows in set (0.00 sec)

Split comma separated string into rows in mysql

Use a subquery of arbitrary digits to split your string.Instead of vals you can use '1,2,3'.

SELECT
  DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(vals, ',', n.digit+1), ',', -1) val
FROM
  tt1
  INNER JOIN
  (SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3  UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) n
  ON LENGTH(REPLACE(vals, ',' , '')) <= LENGTH(vals)-n.digit;

See it working

MySql: Stored Procedure for split, return table

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

MySQL String separation by comma operator

I got the answer

First create new function

CREATE FUNCTION SPLIT_STR(x VARCHAR(255), delim VARCHAR(12), pos INT)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1), delim, '');

Then create stored procedure

DELIMITER ;;
CREATE PROCEDURE Split(in fullstr varchar(255))
BEGIN
    DECLARE a INT Default 0 ;
    DECLARE str VARCHAR(255);

    DROP TABLE IF EXISTS my_temp_table;
    CREATE temporary TABLE my_temp_table(ID INT AUTO_INCREMENT NOT NULL, description text, primary key(ID));

    simple_loop: LOOP
        SET a=a+1;
        SET str=SPLIT_STR(fullstr,",",a);
        IF str='' THEN
            LEAVE simple_loop;
        END IF;
        #Do Inserts into temp table here with str going into the row
        insert into my_temp_table (description) values (str);
   END LOOP simple_loop;
   select * from my_temp_table;
END

After that when i call it by call Split('asas,d,sddf,dfd'); it gives me the output that what i want.

Thanx for every suggestion.

Procedure to loop through comma separated string is not working

Try this one (syntax errors are fixed and without CAST function) -

DELIMITER $$

CREATE PROCEDURE procedure1(IN strIDs VARCHAR(255))
BEGIN
  DECLARE strLen    INT DEFAULT 0;
  DECLARE SubStrLen INT DEFAULT 0;

  IF strIDs IS NULL THEN
    SET strIDs = '';
  END IF;

do_this:
  LOOP
    SET strLen = LENGTH(strIDs);

    UPDATE TestTable SET status = 'C' WHERE Id = SUBSTRING_INDEX(strIDs, ',', 1);

    SET SubStrLen = LENGTH(SUBSTRING_INDEX(strIDs, ',', 1));
    SET strIDs = MID(strIDs, SubStrLen, strLen);

    IF strIDs = NULL THEN
      LEAVE do_this;
    END IF;
  END LOOP do_this;

END
$$

DELIMITER ;

You can debug your procedure with Debugger for MySQL.

EDIT2:

I'd do it without procedure. Try to use FIND_IN_SET function, e.g. -

SET @strIDs = '1,2,3'; -- your id values
UPDATE TestTable SET status = 'C' WHERE FIND_IN_SET(id, @strIDs);

Or create a temp. table, fill it with id values and join these two tables in UPDATE statement.

MySQL Split Comma Separated String Into Temp Table

This is pretty much the same question as Can Mysql Split a column?

MySQL doesn't have a split string function so you have to do work arounds. You can do anything with the data once you split it using one of the methods listed on the answer page above.

You can loop over that custom function and break when it returns empty, you'll have to play and learn some syntax (or at least I would) but the syntax for a FOR loop in mysql is here:
http://www.roseindia.net/sql/mysql-example/for.shtml

You can iterate over it, incrementing the position in the function below:

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

(Credit: https://blog.fedecarg.com/2009/02/22/mysql-split-string-function/ )

Which should return '' if no match is found, so break the loop if no match is found. This will allow you to with only mysql parse over the split string and run the insert queries into a temp table. But man why not just use a scripting language like php for that kind of work? :(

Code for loop syntax:

DELIMITER $$  

CREATE PROCEDURE ABC(fullstr)

   BEGIN
      DECLARE a INT Default 0 ;
      DECLARE str VARCHAR(255);
      simple_loop: LOOP
         SET a=a+1;
         SET str=SPLIT_STR(fullstr,"|",a);
         IF str='' THEN
            LEAVE simple_loop;
         END IF;
         #Do Inserts into temp table here with str going into the row
         insert into my_temp_table values (str);
   END LOOP simple_loop;
END $$

---

Related Topics