MySQL - 查找围绕搜索关键字的单词 [英] MySQL - finding words which surround the searched keyword

问题描述

假设我有一个随机文本，比如这个:

Let's say I have a random text, such as this one:

Lorem ipsum dolor sit amet consectetur adipiscing elit.

使用 MySQL，我想搜索给定的关键字并获得返回以及围绕我的关键字的直接词.

Using MySQL, I'd like to search for a given keyword and get a return along with immediate words surrounding my keyword.

示例:

Select * from table where keyword like "%amet%"

这将返回整个文本.但我需要一个只返回以下文本的查询:

This returns entire text. But I need a query which would return only following text:

sit amet consectetur

所以，我搜索的词，加上上一个和下一个词.

So, the word I've search for, plus previous and next word.

--

另外，如果我搜索关键字elit，返回应该是(因为'elit'后面没有词):

Additionally, In case I search for keyword elit, return should be (as there is no word after 'elit'):

adipiscing elit

如果我搜索关键字Lorem，返回应该是(因为'Lorem'之前没有词):

In case I search for keyword Lorem, return should be (as there is no word before 'Lorem'):

Lorem ipsum

这能以某种方式完成吗?使用正则表达式或检测空格，使用 MID 或某种子字符串?

Can this be somehow done? Using regex or detection of spaces, using MID or some sort of a substring?

推荐答案

我已经玩过了，我可以在纯 mysql 中给你半个解决方案.

I've had a play and I can give you half a solution in pure mysql.

您可以在使用它后获取您所在单词的任一侧的字符串.只是不知道如何获取单词而不是整个子字符串.希望有帮助.

You can get the string either side of the word you're after using this. Just don't know how to get the word rather than the whole substring. Hopefully it's helpful.

select case when (select w.t regexp concat('[[:<:]]', w.v)) = 1 
    then substr(w.t, 1, locate(w.v, w.t)-1) else null end as 'left_word',
       w.v as word,
       case when (select w.t regexp concat(w.v, '[[:>:]]')) = 1 
    then substr(w.t, locate(w.v, w.t)+length(w.v)) else null end as 'right_word'
    from (
        select "Lorem ipsum dolor sit amet consectetur adipiscing elit." as t, "amet" as v
    ) as w;

select case when (select w.t regexp concat('[[:<:]]', w.v)) = 1 
    then substr(w.t, 1, locate(w.v, w.t)-1) else null end as 'left_word',
       w.v as word,
       case when (select w.t regexp concat(w.v, '[[:>:]]')) = 1 
    then substr(w.t, locate(w.v, w.t)+length(w.v)) else null end as 'right_word'
    from (
        select "Lorem ipsum dolor sit amet consectetur adipiscing elit." as t, "elit." as v
    ) as w;

这篇关于MySQL - 查找围绕搜索关键字的单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！