3

I have a Mysql InnoDB table with 10k keywords and I want to match them against several texts.

Some keywords have several words and I only want exact matchs.

Example: Keywords - brown fox, lazy cat, dog, fox, rabbit

Text - The quick brown fox jumps over the lazy dog

I want the query to return - brown fox, dog, fox

Hugo Gameiro
  • 615
  • 2
  • 7
  • 16
  • I have 10k keywords. I cannot go one by one. – Hugo Gameiro Jan 30 '13 at 00:57
  • The example you give is the oposite of what I need. The idea was to go something like full text SELECT * FROM TABLE WHERE MATCH(keywords) AGAINST('The quick brown fox jumps over the lazy dog'); the problem is that the table is InnoDB and doesn't work with full text and full text doesn't return only full match. It would also return partial matchs – Hugo Gameiro Jan 30 '13 at 01:00

3 Answers3

4
SELECT * FROM tenKTable 
WHERE 'The quick brown fox jumps over the lazy dog' LIKE CONCAT('%',keyword,'%')

Source: MySQL SELECT query string matching

Community
  • 1
  • 1
istos
  • 2,654
  • 1
  • 17
  • 19
1

Here's one idea:

SELECT keyword 
FROM Keywords
 JOIN (SELECT 'The quick brown fox jumps over the lazy dog' as col) k
   on k.col like Concat('%',keywords.keyword,'%')

And the SQL Fiddle.

Good luck.

sgeddes
  • 62,311
  • 6
  • 61
  • 83
1

The accepted answer is very good. An improvement would be

SELECT kw FROM keyword
WHERE ' text ' LIKE CONCAT('% ',kw,' %')

Remember to prepend and append space to the text. This way, you avoid partial matches altogether.

Jiulin Teng
  • 299
  • 3
  • 8