fetching image scr from database using sql

I managed to fetch one image detail from the content column of the table table. I think i need to put this in stored procedure. But I am not too sure how to put it or where to start. I need to fetch all the image details and not just one image from the content column of the table.

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

my current query only returns

htt
https://static.google.png
https://static.google2.png

SELECT SUBSTR(src, 1, LOCATE('png',src)+2) AS htt from
        (select substr(
           substr(content, locate('src="', content) + 5), 1 ) as 'src'
        from exampletable
        WHERE substr(
           substr(content, locate('src="', content) + 5), 1 ) LIKE "https%" 
        )A 
WHERE SUBSTR(src, 1, LOCATE('png',src)+2) LIKE '%png' 
    AND  SUBSTR(src, 1, LOCATE('png',src)+2) NOT LIKE '%<%' 
;

Answer

It would be better to do this in your application

If you copy the stored procedure, you need to add DELIMITER at the begining and end, only Mysql Workbench add this automatocally. DBfiddle does it also, so there are no in the example

CREATE TABLE table1
    (`uri` varchar(10), `content` varchar(71), `id` int)
;
    
INSERT INTO table1
    (`uri`, `content`, `id`)
VALUES
    ('/websites/', '<src="https://static.google.png">...< src="https://static.yahoo.png">', 1),
    ('/website1/', '<src="https://static.google2.png">...< src="https://static.yahoo3.png">', 2)
;

CREATE  PROCEDURE `getimages`()
BEGIN
  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;
              END IF;
                IF _content IS NULL then
                   LEAVE getimg;
                END IF;                
                IF LEnGTH (_content) < 4 then
                   LEAVE getimg;
                END IF;
      END LOOP getimg;
  END LOOP getsrc;
  CLOSE curcontent;
SELECT DISTINCT * FROM new_tbl;
END

cALL getimages()

| link                       |
| :------------------------- |
| https://static.google.png  |
| https://static.yahoo.png   |
| https://static.google2.png |
| https://static.yahoo3.png  |

✓

db<>fiddle here