How to split string with '/' in mysql stored procedure?

Question

I am new to stored procedure. How to split a string with '/' and store it in to variable?

I tried the following steps :

bm = 5x112.30 / 5x120.45 ;

SET bp1 = SUBSTRING_INDEX(bm, '/', 1);
SET bp2 = SUBSTRING_INDEX(bm, '/', 2);

But bp2 is always same as bp1. I don't know whats wrong here.

Answer 1

I fixed it by creating a function split(string,delimiter,no). Thi is my code :

DELIMITER $$

CREATE FUNCTION split( str VARCHAR(500), delchar VARCHAR(2), x INT )
RETURNS VARCHAR(500)
BEGIN
RETURN SUBSTR(SUBSTRING_INDEX(str, delchar, x),
LENGTH(SUBSTRING_INDEX(str, delchar, x-1))+IF(x > 1, 2, 1));
END$$

DELIMITER ;

bp1 = split('5x112.30 / 5x120.45','/',1);

bp2 = split('5x112.30 / 5x120.45','/',2);

Answer 2

that's because bm = 5x112.30 it has no / character