MySQL：返回Y字之前和之后的X字

发布于 2024-12-22 07:42:57 字数 513 浏览 0 评论 0原文

我已经尝试了几天了，但无法让它完全按照需要工作，并且没有任何搜索对我有帮助。

在我的数据库中，我有一些带有链接的文章，如下所示：

    one two three four five six seven eight nine ten eleven twelve thirteen fourteen
 <a href="google.com>Google!</a> one two three four five six seven eight nine ten

我想要做的是找到文章中的链接并报告前面最多 5 个单词和后面最多 5 个单词（例如）

所以我的结果是类似于：

ten eleven twelve thirteen fourteen
 <a href="google.com>Google!</a> one two three four five

任何人都可以帮我查询来完成此任务吗？

原文

I've been trying this for a few days now and have been unable to get it working exactly as needed and no amount of searching has helped me.

In my database I have articles with links in them like so:

    one two three four five six seven eight nine ten eleven twelve thirteen fourteen
 <a href="google.com>Google!</a> one two three four five six seven eight nine ten

What I want to be able to do is find the link within the article and report up to 5 words before and up to 5 words after (for example)

So my result would be something like:

ten eleven twelve thirteen fourteen
 <a href="google.com>Google!</a> one two three four five

Can anyone help me with a query to accomplish this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

甜点 2024-12-29 07:42:57

SET @s = 'one two three four five six seven eight nine ten eleven twelve thirteen fourteen
 <a href="google.com>Google!</a> Albuquerque one two three four five six seven eight nine ten';
SELECT CONCAT(
    SUBSTRING_INDEX(LEFT(@s, INSTR(@s, '<a href="') -1), ' ', -6) -- 5 words before ' <a href="'
   , SUBSTRING(@s, INSTR(@s, '<a href="'), INSTR(@s, '</a>') - INSTR(@s, ' <a href="') + 3) -- the href
   , SUBSTRING_INDEX(SUBSTRING(@s, INSTR(@s, '</a>') + 4, 10000), ' ', 6) -- 5 words after '</a>'
   );

请参阅http://dev.mysql.com/doc/refman/ 5.5/en/string-functions.html 文档。

这适用于字符串中的第一个链接。顺便说一句，我会在我的演示代码中执行此操作（在您的例子中是 PHP），但令人惊讶的是您可以在 SQL 中执行此操作。请务必检查您的输入（WHERE quote LIKE '%

SET @s = 'one two three four five six seven eight nine ten eleven twelve thirteen fourteen
 <a href="google.com>Google!</a> Albuquerque one two three four five six seven eight nine ten';
SELECT CONCAT(
    SUBSTRING_INDEX(LEFT(@s, INSTR(@s, '<a href="') -1), ' ', -6) -- 5 words before ' <a href="'
   , SUBSTRING(@s, INSTR(@s, '<a href="'), INSTR(@s, '</a>') - INSTR(@s, ' <a href="') + 3) -- the href
   , SUBSTRING_INDEX(SUBSTRING(@s, INSTR(@s, '</a>') + 4, 10000), ' ', 6) -- 5 words after '</a>'
   );

See http://dev.mysql.com/doc/refman/5.5/en/string-functions.html for the docs.

This works for the first link in the string. BTW, I would do this in my presentation code (PHP in yr case), but it's surprising what you can do in the SQL. Make sure to check your input (WHERE quote LIKE '% <a href="%</a>%') because input with no link will probably not give you what you want.

回复收藏 0 原文