Stored procedure: Split Strings in MySQL

Databases
1

Hello guys, I’ve problem with this stored procedure for ‘Split Strings’ in MySQL.
I need write mysql function to do this.
Can you help me?
Thanks in advance.

ERROR 1064: 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
   'DECLARE x INT DEFAULT 0;

    DECLARE y INT DEFAULT 0;

    SET y = 1;  I' at line 5



SQL Statement:


CREATE PROCEDURE insertEngineer( IN engineerName varchar(256))
BEGIN

    CREATE TABLE IF NOT EXISTS `engineer` (`ID` int(11) NOT NULL auto_increment,`NAME` varchar(256) NOT NULL,PRIMARY KEY (`ID`)) AUTO_INCREMENT=1 ;

    DECLARE x INT DEFAULT 0;
    DECLARE y INT DEFAULT 0;
    SET y = 1;

    IF NOT engineerName IS NULL
    THEN
           SELECT LENGTH(engineerName) - LENGTH(REPLACE(engineerName, ',', '')) INTO @noOfCommas;

           IF  @noOfCommas = 0
          THEN
                 INSERT INTO engineer(NAME) VALUES(engineerName);
          ELSE
                SET x = @noOfCommas + 1;
                WHILE y  <=  x DO
                   SELECT split_string(engineerName, ',', y) INTO @engName;
                   INSERT INTO engineer(NAME) VALUES(@engName);
                   SET  y = y + 1;
                END WHILE;
        END IF;
    END IF;
END
2

:slight_smile:

DELIMITER $$

DROP PROCEDURE IF EXISTS explode_table $$
CREATE PROCEDURE explode_table(bound VARCHAR(255))

  BEGIN

    DECLARE id INT DEFAULT 0;
    DECLARE value TEXT;
    DECLARE occurance INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE splitted_value INT;
    DECLARE done INT DEFAULT 0;
    DECLARE cur1 CURSOR FOR SELECT table1.id, table1.value
                                         FROM table1
                                         WHERE table1.value != '';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    DROP TEMPORARY TABLE IF EXISTS table2;
    CREATE TEMPORARY TABLE table2(
    `id` INT NOT NULL,
    `value` VARCHAR(255) NOT NULL
    ) ENGINE=Memory;

    OPEN cur1;
      read_loop: LOOP
        FETCH cur1 INTO id, value;
        IF done THEN
          LEAVE read_loop;
        END IF;

        SET occurance = (SELECT LENGTH(value)
                                 - LENGTH(REPLACE(value, bound, ''))
                                 +1);
        SET i=1;
        WHILE i <= occurance DO
          SET splitted_value =
          (SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(value, bound, i),
          LENGTH(SUBSTRING_INDEX(value, bound, i - 1)) + 1), ';', ''));

          INSERT INTO table2 VALUES (id, splitted_value);
          SET i = i + 1;

        END WHILE;
      END LOOP;

      SELECT * FROM table2;
    CLOSE cur1;
  END; $$

CALL explode_table(',');