(`uri` varchar(10), `content` varchar(71), `id` int)

;

INSERT INTO table1

(`uri`, `content`, `id`)

('/websites/', '<src="https://static.google.png">...< src="https://static.yahoo.png">', 1),

('/website1/', '<src="https://static.google2.png">...< src="https://static.yahoo3.png">', 2)

;

DECLARE finished INTEGER DEFAULT 0;

DECLARE _content TEXT DEFAULT "";

-- declare cursor for employee email

DEClARE curcontent

CURSOR FOR

SELECT `content` FROM table1;

-- declare NOT FOUND handler

DECLARE CONTINUE HANDLER

FOR NOT FOUND SET finished = 1;

DROP TEMPORARY TABLE IF EXISTS new_tbl;

CREATE TEMPORARY TABLE new_tbl (link VARCHAR(100));

OPEN curcontent;

getsrc: LOOP

FETCH curcontent INTO _content;

IF finished = 1 THEN

LEAVE getsrc;

END IF;

-- build email list

getimg: LOOP

sET @a := locate('src="', _content);

if locate('src="', _content) = 0 THEN

LEAVE getimg;

END IF;

INSERT INTO new_tbl VALUES (LEFT(SUBSTR(_content, locate('src="', _content) + 5),locate('.png"', substr(_content, locate('src="', _content) + 5)) +3));

IF _content != RIGHT(_content, LOCATE('png',_content)+9) then

SET _content := RIGHT(_content, LOCATE('png',_content)+9);

ELSE

LEAVE getimg;