使用 mysql 查询从 img src 中提取 URL [英] Extract the URL from img src with a mysql query

问题描述

test_instructions 的内容:

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

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

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

我使用过:Boann strip_tags 功能

select strip_tags(test_instructions) from test_tb

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

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

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

推荐答案

这里有一个工作示例:

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:可执行演示

SQL Fiddle: executeable demo

这篇关于使用 mysql 查询从 img src 中提取 URL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！