CODEWITHSUNDEEP
mysqlstringmatchkeyword
Hugo Gameiro
Hugo Gameiro

Reputation: 625

Mysql find keywords in text

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

Upvotes: 3

Views: 2745

Answers (3)

Jiulin Teng
Jiulin Teng

Reputation: 364

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.

Upvotes: 1

istos
istos

Reputation: 2662

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

Source: MySQL SELECT query string matching

Upvotes: 4

sgeddes
sgeddes

Reputation: 62861

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.

Upvotes: 1

Related Questions