Extract multiple img src from long text in select mysql

Question

I have a table named posts.

It contains id and post_content:

ID  post_title post_content
1   aaaa       <p>AAaa</p><img src="https://domain/uploads/aa.jpg"><p>Addd<img src="https://domain/uploads/assssa.jpg"></p>
2   bbb        <img src="https://domain/uploads/bbbb.jpg"><p>xxxxx<img src="https://domain/uploads/xxxxx.jpg"></p>
3   ccc        <p>aaaaa</p><img src="https://domain/uploads/aa.jpg">

I want to extract the image src from each post_content with specific domain. Each post_content has multiple images.

Like I only want to get images with url like this:

<img src="https://aaaaa.co.jp/uploads/">

Note: <img src=""> can have width, height, class or id inside.

Like this:

<img src="" class="" id="">

OR

<img width="" height="" src="" class="" id="">.

I already tried this one from here: Extract the URL from img src with a mysql query

Some of it returns the image url but some of it wont.

This is my query so far:

SELECT 
    post.ID,
    post.post_title,
    SUBSTR(SUBSTR(post.post_content,
            LOCATE('src="', post.post_content) + 5),
        1,
        LOCATE('"',
                SUBSTR(post.post_content,
                    LOCATE('src="', post.post_content) + 5)) - 1) AS 'src'
FROM
    tokyo_posts post;

So this is my desired output:

ID  post_title post_content
1   aaaa       https://domain/uploads/aa.jpg
1   aaaa       https://domain/uploads/assssa.jpg
2   bbb        https://domain/uploads/bbbb.jpg
2   bbb        https://domain/uploads/xxxxx.jpg
3   ccc        https://domain/uploads/aa.jpg

Note: I'm using a wordpress database. I'm gonna use this query for import image purposes.

Mysql version: 5.6.42

Doing this in PHP seems a better option to me. – nice_dev Mar 13 '19 at 09:07 — nice_dev, Mar 13 '19 at 09:07

Arnaud Peralta · Answer 1 · 2019-03-13T09:21:50.960

0

If you don't change the structure of your datas, this one is perfect for you.

SELECT post.ID,
post.post_title,
SUBSTRING(post_content, INSTR(post_content, 'src="') + 5
, INSTR(post_content, '">') - (INSTR(post_content, 'src="') + 5))
FROM
tokyo_posts post;

edited Mar 13 '19 at 09:21

answered Mar 13 '19 at 09:08

Arnaud Peralta

1,287
1
16
20

I haven't tested this but there maybe more attributes after `src` tag. So, calculating it this way assumes that `img` tag ends after `src` attribute since it looks for `">`. Also, as OP mentioned, the query needs to capture all image tags in the `post_content` and report it as a new row. – nice_dev Mar 13 '19 at 09:36

Extract multiple img src from long text in select mysql

1 Answers1