CODEWITHSUNDEEP
mysqlstored-procedures
Tausif Khan
Tausif Khan

Reputation: 2278

MySql, split a string and insert into table

I have two inputs for my stored procedure. One is the 'RoledID' and second one is the 'MenuIDs'. 'MenusIDs' is a list of comma separated menus ids that need to be inserted with RoledID. RoleId is just an INT and we need to put this RoledID against each MenuID. My table 'RolesMenus' contains two columns one for MenuID and one for RoleID.

Now I need to split MenuIDs and insert each MenuID with RoleID.

How can I write a stored procedure for it?

Upvotes: 11

Views: 13687

Answers (5)

Hardeep Singh
Hardeep Singh

Reputation: 780

First create procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `split_str_save_to_tmp_table`(
IN _str TEXT,
IN _table_name VARCHAR(80)
)
BEGIN

#DROP FIRST OLD TABLE
SET @q = CONCAT('DROP TEMPORARY TABLE IF EXISTS ', _table_name);
PREPARE st FROM @q;
EXECUTE st;

#CREATE TABLE
SET @q = CONCAT('CREATE TEMPORARY TABLE ', _table_name, '(id INT UNSIGNED NOT NULL PRIMARY KEY (id) )' );
PREPARE st FROM @q;
EXECUTE st;

SET @ids = REPLACE(_str, ',', '),(');
SET @ids = CONCAT('(', @ids, ')');

#INSERT INTO TABLE
SET @q = CONCAT('INSERT INTO ' ,  _table_name ,' VALUES');
SET @q = CONCAT(@q, @ids);  

PREPARE st FROM @q;
EXECUTE st;
DEALLOCATE PREPARE st;

END

Then call

call split_str_save_to_tmp_table('1,2,3,4,5', 'tmp_split_product');

SELECT * FROM tmp_split_product

Upvotes: 0

Juan Carlos Marcano Trias
Juan Carlos Marcano Trias

Reputation: 149

for this solution, you must create a table with the name split_table, it can have a id(autoincrement) if you need it and must have a column where to store the value (I call it valor)

DELIMITER $$

USE `dbaname`$$

DROP PROCEDURE IF EXISTS `Split`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Split`(
    IN cadena VARCHAR(8000),
    IN delimitador VARCHAR(10)
    )
BEGIN

    TRUNCATE split_table;

    SET @posicion = 1;
    SET @ldel = LENGTH(delimitador);     
    SET @valor = SUBSTRING_INDEX(cadena, delimitador, 1);

    WHILE @valor <> '' AND @posicion > 0 DO

        SET @valor = SUBSTRING_INDEX(cadena, delimitador, 1);

        INSERT INTO split_table(valor) VALUES (@valor);

        SET @posicion = POSITION(delimitador IN cadena);
        SET @largo = LENGTH(cadena);

        IF @largo >= @posicion THEN
            SET cadena = SUBSTR(cadena, @posicion + @ldel, @largo - @posicion);
            SET @valor = SUBSTRING_INDEX(cadena, delimitador, 1);
        ELSE
            SET @posicion = 0;
        END IF;

    END WHILE;

    END$$

DELIMITER ;

Upvotes: 0

Devart
Devart

Reputation: 121952

You can build one INSERT query (because statement allows to insert multiple records) and run it with prepared statements, e.g. -

SET @MenuIDs = '1,2,3';
SET @RoledID = 100;

SET @values = REPLACE(@MenuIDs, ',', CONCAT(', ', @RoledID, '),('));
SET @values = CONCAT('(', @values, ', ', @RoledID, ')'); -- This produces a string like this -> (1, 100),(2, 100),(3, 100)

SET @insert = CONCAT('INSERT INTO RolesMenus VALUES', @values); -- Build INSERT statement like this -> INSERT INTO RolesMenus VALUES(1, 100),(2, 100),(3, 100)

-- Execute INSERT statement
PREPARE stmt FROM @insert;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

As you see, it can be done without stored procedure.

Upvotes: 22

Tom Mac
Tom Mac

Reputation: 9853

Give this a go. It may need some tweaking if the MenuIDs string does not conform to 'menuId,menuId,menuId'.

Also I do not know what data type the menuId column is in your target table (INT?) so you may have to put some numeric checking in too (in case '1,2,3,banana,4,5' is passed in as the MenuIds input parameter).

DELIMITER $$

DROP PROCEDURE IF EXISTS `insert_role_menuids`$$

CREATE PROCEDURE `insert_role_menuids`(IN RoleID INT,IN MenuIDs varchar(500))
BEGIN
declare idx,prev_idx int;
declare v_id varchar(10);

set idx := locate(',',MenuIDs,1);
set prev_idx := 1;

WHILE idx > 0 DO
 set v_id := substr(MenuIDs,prev_idx,idx-prev_idx);
 insert into RolesMenus (RoleId,MenuId) values (RoleID,v_id);
 set prev_idx := idx+1;
 set idx := locate(',',MenuIDs,prev_idx);
END WHILE;

set v_id := substr(MenuIDs,prev_idx);
insert into RolesMenus (RoleId,MenuId) values (RoleID,v_id);

END$$
DELIMITER ;

Upvotes: 5

dwalldorf
dwalldorf

Reputation: 1379

AFAIK MySQL does not have a function to split strings. Here is the MySQL manual for string related functions. In the comments section should be some information about workarounds for splitting string with substring-functions but not really usable: MySQL manual

Upvotes: -2

Related Questions