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
分享 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.
分享
