1377

How reduce this code in mysql for separate a given string

Question:

USE `coordinates`$$ DROP PROCEDURE IF EXISTS `testx2`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `testx2`(x1 VARCHAR(225),delim VARCHAR(3)) BEGIN DECLARE str VARCHAR(225); DECLARE str2 VARCHAR(225); DECLARE str3 VARCHAR(225); DECLARE len INT DEFAULT 0; DECLARE templen INT DEFAULT 0; SET str = x1; SET @str2 = SUBSTRING_INDEX(str,',',1); /*col 1*/ SET @len1 = CHAR_LENGTH(@str2)+1; SET @templen = @len1 ; SET @str3 = SUBSTR(SUBSTRING_INDEX(str,',',2),@templen+1); /*col 2*/ SET @len2 = CHAR_LENGTH(@str3)+1; SET @templen = @len1 + @len2; SET @str4 = SUBSTR(SUBSTRING_INDEX(str,',',3),@templen+1); /*col 3*/ SET @len3 = CHAR_LENGTH(@str4)+1; SET @templen = @len1 + @len2 + @len3; SET @str5 = SUBSTR(SUBSTRING_INDEX(str,',',4),@templen+1); /*col 4*/ SET @len4 = CHAR_LENGTH(@str5)+1; SET @templen = @len1 + @len2 + @len3 + @len4; SET @str6 = SUBSTR(SUBSTRING_INDEX(str,',',5),@templen+1); /*col 5*/ SET @len5 = CHAR_LENGTH(@str6)+1; SET @templen = @len1 + @len2 + @len3 + @len4 + @len5; SET @str7 = SUBSTR(SUBSTRING_INDEX(str,',',6),@templen+1); /*col 6*/ SET @len6 = CHAR_LENGTH(@str7)+1; SET @templen = @len1 + @len2 + @len3 + @len4 + @len5 + @len6; SET @str8 = SUBSTR(SUBSTRING_INDEX(str,',',7),@templen+1); /*col 7*/ SET @len7 = CHAR_LENGTH(@str8)+1; SET @templen = @len1 + @len2 + @len3 + @len4 + @len5 + @len6 + @len7; SET @str9 = SUBSTR(SUBSTRING_INDEX(str,',',8),@templen+1); /*col 8*/ SET @len8 = CHAR_LENGTH(@str9)+1; SET @templen = @len1 + @len2 + @len3 + @len4 + @len5 + @len6 + @len7 + @len8; SET @str10 = SUBSTR(SUBSTRING_INDEX(str,',',9),@templen+1); /*col 9*/ SET @str11 = SUBSTRING_INDEX(str,',',-1);/*col 10*/ INSERT INTO test2(t1,t2,t3,t4,t5,t6,t7,t8,t9,t10) VALUES(@str2,@str3,@str4,@str5,@str6,@str7,@str8,@str9,@str10,@str11); SELECT * FROM test2; END$$ DELIMITER ;

Answer1:

Your variable declarations are not needed here. It's a good idea to use the delim parameter of the procedure too, so you could write the same as:

DROP PROCEDURE IF EXISTS `testx2`; DELIMITER $$; CREATE DEFINER=`root`@`localhost` PROCEDURE `testx2`(x1 VARCHAR(225),delim VARCHAR(3)) BEGIN INSERT INTO test2(t1,t2,t3,t4,t5,t6,t7,t8,t9,t10) VALUES( SUBSTRING_INDEX(SUBSTRING_INDEX(x1, delim, 1), delim, -1), SUBSTRING_INDEX(SUBSTRING_INDEX(x1, delim, 2), delim, -1), SUBSTRING_INDEX(SUBSTRING_INDEX(x1, delim, 3), delim, -1), SUBSTRING_INDEX(SUBSTRING_INDEX(x1, delim, 4), delim, -1), SUBSTRING_INDEX(SUBSTRING_INDEX(x1, delim, 5), delim, -1), SUBSTRING_INDEX(SUBSTRING_INDEX(x1, delim, 6), delim, -1), SUBSTRING_INDEX(SUBSTRING_INDEX(x1, delim, 7), delim, -1), SUBSTRING_INDEX(SUBSTRING_INDEX(x1, delim, 9), delim, -1), SUBSTRING_INDEX(SUBSTRING_INDEX(x1, delim, 9), delim, -1), SUBSTRING_INDEX(x1, delim, -1), ); SELECT * FROM test2; END$$ DELIMITER ;

Using a loop won't help here. Please regard the nested call of SUBSTRING that eliminates the need of your @templen variable.

Recommend