CODEWITHSUNDEEP
mysqlsql
InsaneCoder
InsaneCoder

Reputation: 8268

Pulling a substring starting and ending with a special character from rows of a MySQL table

I have a table articles with a column body.

Inside body column, I have a lot of rows having <img> tags, e.g

----------------------------------------
           body
----------------------------------------
This is <img src="www.abc.com/1"> artcle 1
This is article 2
This is <img src="www.abc.com/2"> article 3
This is article 4

Now I want to get all the src portions, like www.abc.com/1,www.abc.com/2,etc.

I got all such rows using 

select body from articles where body like `%img%`

How can I extract only the link (src) portion?

Upvotes: 1

Views: 369

Answers (1)

John Ruddell
John Ruddell

Reputation: 25842

you can use substr method with locate and locate reverse to find the beginning double quote and the ending double quote from your src="www.abc.com/1" like so:

SELECT 
    body,
    SUBSTR(
        body, 
        LOCATE('"',body)+1, 
        (CHAR_LENGTH(body) - LOCATE('"',REVERSE(body)) - LOCATE('"',body))
    ) AS 'SRC URL'
FROM articles 
WHERE body LIKE '%img%'

DEMO

Upvotes: 1

Related Questions