1

With current MySql query is able to fetch src from the first img tag.

Query:

SELECT substr(substr(textfield , locate('src="', textfield ) + 5), 1, 
                     locate('"', substr(textfield , locate('src="', textfield ) + 5)) - 1) as 'imgurl' 
from pages where field1 ='data' and field2 = 'nothing';

Need to fetch all the src in all img tag in the cell of a row.

Example:

table column has id and text.

textfield has 

some random content <img src='https://www.fakeurl.com/folder/image.jpeg'> 
and also <img src="https://www.fakeurl.com/folder/image.jpg"> and also <img 
src='/folder/image.jpeg'> and some additional content

Need to fetch all src from this textfield column using a query or a function or procedure in MySQL.

Result needed is:

https://www.fakeurl.com/folder/image.jpeg
https://www.fakeurl.com/folder/image.jpg
/folder/image.jpeg

What is needed to be modified to get result, or using some other query?

APC
  • 144,005
  • 19
  • 170
  • 281
cyberviral
  • 65
  • 1
  • 7
  • Are you saving entire HTML pages in a database? – dustytrash Sep 11 '18 at 18:50
  • There's a few ideas here you could use: [SQL split values to multiple rows](https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows) – Rup Sep 11 '18 at 18:52
  • SQL and HTML are really not friends as HTML can be expressed in ways that are really difficult to parse with a regular expression. – tadman Sep 11 '18 at 18:59
  • 1
    It's not wierd to store HTML in SQL when you are using a CMS.. But it is kind off wierd to parse HTML with SQL because it's better done on application code.. i assume you are not using MySQL 8.0+? – Raymond Nijland Sep 11 '18 at 19:21
  • Yes. I am saving entire html in the database. my mysql database version is 8.0.11. I would like to have this done on mysql instead of application code. – cyberviral Sep 11 '18 at 20:01
  • For future reference please don't tag your questions with irrelevant tags. You may think you are attracting additional eyeballs to your question but really you just want people who are expert in MySQL. – APC Sep 12 '18 at 05:07

0 Answers0