mysql - 使用 mysql 查询从 img src 中提取 URL

Question

test_instructions 的内容:

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

如何从 mysql 列的数据中提取 URL？

我用过:Boann strip_tags funtion

select strip_tags(test_instructions) from test_tb

它适用于所有其他 html 标签，但对于 img src 它显示为 null。
怎么可能只从 img src 获取 URL？

如果该列为空，我会收到此错误:

#2014 - command out of sync; you can't run this command now

Answer 1

这是一个适合您的示例:

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