SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Stored procedure: Split Strings in MySQL

    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.
    Code:
    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. #2
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)


    Code:
    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(',');


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •