在Mysql中搜索单词并从字符串(非完整字符串)中获取预测性搜索单词

Question

我有下面列出的SQL表设计

*-----------------------------------------
| id | title                              |
| -- | -----------------------------------|
| 1  | This is nice pen looking good      |
| 2  | This is nice pen looking elegent   |
| 3  | This is nice pen looking great     |
| 4  | This is nice pen looking best.     |
------------------------------------------

查询示例：Select * from table where title LIKE '%looking%'

当我尝试搜索单词"strong"时；看着"使用(如查询(或使用[正则表达式]如示例查询，我得到了下面提到的完整字符串结果

结果

*------------------------------------
| title                              |
| -----------------------------------|
| This is nice pen looking good      |
| This is nice pen looking elegent   |
| This is nice pen looking great     |
| This is nice pen looking best.     |
-------------------------------------

我想要什么

我想要预先激活的搜索词(不是完整的字符串(

如何使用SQL搜索单词(looking(来获得下面提到的结果

*-------------------
| title             |
| ------------------|
| looking good      |
| looking elegent   |
| looking great     |
| looking best.     |
--------------------

请建议我如何编写查询以获得这些类型的结果？感谢

Answer 1

您可以使用substring_index():获得第一个下一个作品

Select t.*,
substring_index(substring_index(concat(' ', title, ' '), ' looking ', -1), ' ', 1) as next-word
from table
where title LIKE '%looking%';

这里有一个db<gt；不停摆弄