By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 10.4.26-MariaDB-1:10.4.26+maria~deb10-log |
CREATE TABLE articles ( id int, body text );
INSERT INTO articles VALUES
(1, 'some blah <img src="123456.jpg"> and some more <img alt="" src="789012.png"> and yet more <img src="345678.png">'),
(2, 'no images in this one'),
(3, 'some blah <img src="35.jpg"> and some more <img src="71.png"> and yet more <img src="37.png">'),
(4, 'alt no src <img alt="bl4h"> and some more text <img class="someClass" src="432.bmp">');
Records: 4 Duplicates: 0 Warnings: 0
CREATE FUNCTION FIND_IMG_SRC(str text, occurrence int)
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
DECLARE loc INT DEFAULT 1;
DECLARE src_loc INT;
DECLARE i INT DEFAULT 0;
DECLARE img_tag text DEFAULT '';
WHILE(i < occurrence AND loc IS NOT NULL) DO
SET loc = NULLIF(LOCATE('<img', str, loc + 1), 0);
SET i = i + 1;
END WHILE;
IF loc IS NULL THEN
RETURN NULL;
ELSE
# Get complete img tag
SET img_tag = SUBSTR(str, loc, LOCATE('>', str, loc + 1) - loc + 1);
# Check that img tag contains src attribute
SET src_loc = LOCATE('src="', img_tag);
IF src_loc = 0 THEN
RETURN NULL;
ELSE
# Return content of src attribute
RETURN SUBSTRING_INDEX(SUBSTR(img_tag, src_loc + 5), '"', 1);
END IF;
END IF;
END
SELECT a.id, FIND_IMG_SRC(body, t.n) AS img
from articles a
CROSS JOIN (
SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
) AS t
HAVING img IS NOT NULL
ORDER BY id, img;
| id | img |
|---|---|
| 1 | 123456.jpg |
| 1 | 345678.png |
| 1 | 789012.png |
| 3 | 35.jpg |
| 3 | 37.png |
| 3 | 71.png |
| 4 | 432.bmp |