从mysql中的文本字段提取特定词 [英] Extract specific words from text field in mysql

问题描述

我有一个包含文本字段的表，根据行的大小，在字段中有大约3到4个句子。

现在， - 完成html对象，我想开始输入一个单词的开头，并且数据库返回以数据库文本字段中的那些字母开头的单词。

示例的文本字段：我喜欢鱼饵，fishhat是太好了

在我的自动完成如果我将输入鱼，它会建议鱼腿

一切正常。

我可以轻松找到包含特定字词的列，

 从mytable匹配中选择data_txt（data_txt）against（'fish' IN BOOLEAN模式）限制10 
  

我知道它很脏，但是我不能重新排列数据库。 p>

感谢您的帮助！

编辑：

这是我得到的，由于布伦特·斯文，它不干净，但它的工作原理：

  SELECT DISTINCT 
 SUBSTRING （data_txt，
 LOCATE（'great'，data_txt），
 LOCATE（''，data_txt，LOCATE（'great'，data_txt）） -  LOCATE（'great'，data_txt）
） 
 FROM mytable WHERE data_txt LIKE'％great％'
 LIMIT 10 
  

any关于如何避免重复使用相同的 LOCATE 表达式的想法？

解决方案

使用 LOCATE 查找该单词的出现。

使用 LOCATE 和上一个 LOCATE 返回值来查找单词后第一个空格的出现。

USE SUBSTR 和前两个 LOCATE 返回值来提取整个单词。

HTH。

I have a table that contains a text field, there is around 3 to 4 sentences in the field depending on the row.

Now, I am making an auto-complete html object, and I would like to start typing the beginning of a word and that the database return words that start with those letters from the database text field.

Example of a text field: I like fishsticks, fishhat are great too

in my auto-complete if I would type "fish" it would propose "fishsticks" and "fishhat"

Everything works but the query.

I can easily find the rows that contains a specific word but I can't extract only the word, not the full text.

select data_txt from mytable match(data_txt) against('fish', IN BOOLEAN MODE) limit 10

I know it is dirty, but I cannot rearrange the database.

Thank you for your help!

EDIT:

Here's what I got, thanks to Brent Worden, it is not clean but it works:

SELECT DISTINCT
SUBSTRING(data_txt, 
LOCATE('great', data_txt),
LOCATE(' ' , data_txt, LOCATE('great', data_txt)) - LOCATE('great', data_txt)
)
 FROM mytable WHERE data_txt LIKE '% great%'
LIMIT 10

any idea on how to avoid using the same LOCATE expression over and over?

解决方案

Use LOCATE to find the occurrence of the word.

Use LOCATE and the previous LOCATE return value to find the occurrence of the first space after the word.

USE SUBSTR and the previous 2 LOCATE return values to extract the whole word.

HTH.

这篇关于从mysql中的文本字段提取特定词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！