返回直接出现在“感兴趣的单词”之前的单词列表

Question

实际上，我有一列长文本值，例如：

ID | text 
0  | Hi my name is Brian.
1  | I think Brian sucks.

我想写一个函数，返回紧跟在“感兴趣的单词”之前的单词列表。因此，如果我搜索"Brian"，函数将返回"is“和"think”，因为这两个词都出现在"Brian“之前。到目前为止，我有以下代码，但它不起作用：

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
    from (
        select text from table as t, "Brian" as v
    ) as w;

有什么想法吗？

Answer 1

您的查询中存在语法错误：

select text from table as t, "Brian" as v

应该是：

select text as t, "Brian" as v from table

一旦你解决了这个问题，你的输出是：

Hi my name is
I think

然后，您可以使用SUBSTRING_INDEX从这些字符串中提取最后一个单词：

select case when w.t regexp concat('[[:<:]]', w.v, '[[:>:]]') 
            then substring_index(substr(w.t, 1, locate(w.v, w.t)-2), ' ', -1)
            else null
       end as 'left_word',
       w.v as word
from (
  select text as t, "Brian" as v
  from `table`
) as w;

输出：

left_word   word
is          Brian
think       Brian

Demo on SQLFiddle

注对于MySQL > 8.0.4，您需要使用\\b而不是[[:<:]]和[[:>:]]作为单词边界，因此您的查询将变为

select case when w.t regexp concat('\\b', w.v, '\\b') 
            then substring_index(substr(w.t, 1, locate(w.v, w.t)-2), ' ', -1)
            else null
       end as 'left_word',
       w.v as word
from (
  select text as t, "Brian" as v
  from `table`
) as w;

Demo on dbfiddle

Answer 2

您可以尝试执行以下查询：

select 
SUBSTRING_INDEX(SUBSTRING_INDEX(text, 'Brian', 1),' ',-2) 
from `table` 
where text LIKE '%Brian%'