MySQL returns exact word match from text content

Question

I would like to write a query which can compare words from string and returns exact match instead of like.

Lets say I've a field called txt which have value "Hello, I am string"

Now if I search for "am" then it should return that row if I search for "I" or "string" or "Hello," then it should return this row but if I search for "a", "tri" or "str" then it should not return anything

Use full text search: https://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html. — Gordon Linoff, Jul 09 '15 at 11:41
You can use MySql REGEXP, it already answered. http://stackoverflow.com/questions/656951/search-for-whole-word-match-in-mysql — Naveen, Jul 09 '15 at 11:49

Gordon Linoff · Accepted Answer · 2015-07-09T20:01:18.117

1

The right answer is probably full text search.

However, there are two caveats. If you have short strings, such as product descriptions or user comments, and want to use like, you can do something like this:

where concat(' ', txt, ' ') like concat('% ', $word, ' %')

However, this assumes that the delimiters are spaces. So, it would not find "Hello". You can fix this by doing:

where concat(' ', replace(txt, ',' ' '), ' ') like concat('% ', $word, ' %')

But you'll quickly find that this is a pain. Hence: full text search.

Second, if you are really storing keywords in the column, then the solution is simpler. Don't do that. Create a junction table that has one row per original table row and one per keyword. Storing lists in strings is a bad idea in SQL.

edited Jul 09 '15 at 20:01

answered Jul 09 '15 at 11:43

Gordon Linoff

1,242,037
58
646
786

I'm trying it just 2 mins. I'll let you know and accept your answer if it works :) – Mohit Bumb Jul 09 '15 at 12:27
not returning any records also not giving any errors – Mohit Bumb Jul 10 '15 at 04:52
1

@MohitBumb . . . Perhaps there is a case sensitivity issue. You can try to use `lower()` to put them in the same case. – Gordon Linoff Jul 11 '15 at 11:34
it works, there's some minor issue but issue solved thanks – Mohit Bumb Jul 13 '15 at 08:56

MySQL returns exact word match from text content

1 Answers1

Linked