1

Content of test_instructions:

<img src="http://www.test.com/quiz_images/poster_from_postermywall.jpg" alt="" align="" border="0px">

How can I extract the URL from the data of a mysql column?

I have used: Boann strip_tags funtion

select strip_tags(test_instructions) from test_tb

It works for all other html tags but for img src it shows as null.
How is it possible fetch only the URL from img src?

If the column is null I get this error:

#2014 - command out of sync; you can't run this command now
Community
  • 1
  • 1
dude
  • 4,532
  • 8
  • 33
  • 51
  • The function you use removes every tag. That's why your result is empty, because the whole img tag is removed. – stefan Feb 25 '15 at 19:09

1 Answers1

2

Here is a working example for you:

CREATE TABLE test 
(
  id int auto_increment primary key, 
  details varchar(250)
);

INSERT INTO test (details) VALUES ('<img src="http://www.test.com/quiz_images/poster_from_postermywall.jpg" alt="" align="" border="0px">');

select substr(
   substr(details, locate('src="', details) + 5), 
   1, 
   locate('"', substr(details, locate('src="', details) + 5)) - 1
) as 'src'
from test;

SQL Fiddle: executeable demo

stefan
  • 4,958
  • 4
  • 20
  • 37