return all regex match from each row of a table in mysql

Question

I have a table named 'Articles' that has some rows, inside each row has some image tag that I want to return all of those.

I used this query but it only returns one of those tags per row. And other lines are returned empty.

SELECT REGEXP_SUBSTR(body, '([0-9])+\.(jpg|png|gpeg)') from articles;

Article 1:
    <img src"54545343.png" />
    <img src"24352445.png" />
    <img src"24352435.png" />

article 2: 
<img src"24352435.png" />

article 3:
...

I want all of these images.

thank you for your help

UPDATE version 10.4.19-MariaDB

user1191247 · Accepted Answer · 2023-06-13T13:34:38.127

1

You could use a "dirty solution" like this, but it is horribly inefficient:

SELECT a.id, REGEXP_SUBSTR(body, '([0-9])+\.(jpg|png|gpeg)', 1, t.n) AS img
FROM articles a
CROSS JOIN (
    VALUES
        ROW(1), ROW(2), ROW(3), ROW(4), ROW(5),
        ROW(6), ROW(7), ROW(8), ROW(9), ROW(10)
) AS t(n)
HAVING img IS NOT NULL
ORDER BY id, img;

The fourth parameter to REGEXP_SUBSTR is:

occurrence: Which occurrence of a match to search for. If omitted, the default is 1.

The above query tries to retrieve occurrences 1 - 10.

This is probably a good example of

"just because you can, does not mean you should"

but you could use a stored function to extract the content of the image src attributes:

DELIMITER //
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//
DELIMITER ;

And then the following query:

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;

And here's a db<>fiddle.

Note: the above stored function expects src="..." and will not work with single quotes or spaces either side of the =. It will also fail if there is a > anywhere inside the <img>.

edited Jun 13 '23 at 13:34

answered Jun 12 '23 at 13:05

user1191247

10,808
2
22
32

thank you for your answer but your query produce error #1582 - Incorrect parameter count in the call to native function 'REGEXP_SUBSTR' @user1191247 – ali Jun 12 '23 at 13:16
10.4.19-MariaDB @user1191247 – ali Jun 12 '23 at 13:39
1

Please update your question, as MySQL and MariaDB are not the same thing! – user1191247 Jun 12 '23 at 13:46
1

Unfortunately, [MariaDB's `REGEXP_SUBSTR`](https://mariadb.com/kb/en/regexp_substr/) does not currently support the additional parameters offered in MySQL. You may have to resort to processing this in your programming language of choice. – user1191247 Jun 12 '23 at 14:04
yes. I want all of the names of the images in each row @user1191247 – ali Jun 14 '23 at 17:09
MySQL returned an empty result set (i.e. zero rows). (Query took 0.0181 seconds.) @user1191247 – ali Jun 14 '23 at 17:13
thank you so much. your query worked :))))) @user1191247 – ali Jun 14 '23 at 17:38
please rate my question to :)) – ali Jun 14 '23 at 17:38
i accept you answer thanks again :) @user1191247 – ali Jun 14 '23 at 17:43
1

Thanks. The sample data added to your question is missing `=` for the src attributes. – user1191247 Jun 14 '23 at 17:45

return all regex match from each row of a table in mysql

1 Answers1