Search for records in my db which match/contain certain WHOLE keyword(s)

Question

I am looking for a way to search for records in my db which match/contain a certain keyword(s). Now these keyword(s) have to be its own word and not part of another word. So if the keyword is "wich", I don't want it to match records with sand"wich" in the title.

The only way I can think of doing this is by using LIKE but I can't even get that to work.

I tried using the following:

SELECT * FROM tbl_recipes WHERE title LIKE '%$term%'

but that matches all records where $term appears anywhere in the title regardless of whether its part of another word or not.

So I thought this might work

SELECT * FROM tbl_recipes WHERE title LIKE '% $term %'

but this never returns any records.

can anyone see where I'm going wrong? or if there is a better, more suitable function other than LIKE that I should use?

If you are using `contains` and `go`, why is the question tagged "mysql". Are you using MySQL or SQL Server? — Gordon Linoff, Jul 01 '14 at 15:06
i was of the understanding CONTAINS is a mysql function? but like i said in the question i've not used CONTAINS before so im not sure if it will do what i want? im using mysql :) thanks — user2886669, Jul 01 '14 at 15:09
Your second example should work, is `$term` not being evaluated correctly? `SELECT * FROM tbl_recipes WHERE title LIKE '% soup %'` will do what you expect — Tom, Jul 01 '14 at 15:13
I don't see `CONTAINS()` in a list of mysql string functions: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html — Hart CO, Jul 01 '14 at 15:13
ah thats probably why i cant get it to work then if its not in the mysql function list :( i'll edit my question. — user2886669, Jul 01 '14 at 15:16
@tom i've tried the second example again and still wont work, i've even tried it with the value of $term instead of the varible but still didnt return any rows? — user2886669, Jul 01 '14 at 15:17
hi jens yes you are correct this is very similar to the above, appoligies as i didn't see this when looking, i think i have the answer from that thread CONCAT('%', @email , '%'); seems to work, can someone briefly explain what concat does? thanks and sorry for the similar thread! — user2886669, Jul 01 '14 at 15:29
Concat concatinating string. See [mysql doc](http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat) — Jens, Jul 01 '14 at 15:31
I don't understand why this was closed as a "duplicate". The question marked as a duplicate is about referencing user-defined variables; this question seems to be about matching "whole words" within a string, using `LIKE` or some other operator or function. I don't see that this question asked about how to reference MySQL user-defined variables within a SQL statement. (MySQL user variables name start with an **`@`** character, not a **`$`** character.) — spencer7593, Jul 01 '14 at 18:01

spencer7593 · Answer 1 · 2014-07-01T15:59:05.233

1

If I wanted to locate rows that had an occurrence of the word 'soup' contained in the title column, which was not part of another word, I could do something like this:

SELECT r.*
  FROM tbl_recipies r
 WHERE r.title LIKE 'soup'           
    OR r.title LIKE 'soup %'
    OR r.title LIKE '% soup'
    OR r.title LIKE '% soup %'

That checks for a match 1) entire title, 2) as first word in title, 3) as last word in title, 4) as word within the title.

This assumes that "words" within the title are delimited by one or more spaces, not commas, dashes, or periods.

MySQL also has a REGEXP function that can perform matches using regular expressions. You would still need to do the same kind of checks.

Another approach would be to use the FIND_IN_SET function. To use that, you could replace all space characters with a comma.

SELECT r.*
  FROM tbl_recipes r
 WHERE FIND_IN_SET('soup',REPLACE(r.title,' ',','))

This approach lends itself to replacing characters other than space with commas; we could also replace dashes with commas, and tabs with commas, and so on, by wrapping the REPLACE expression in other REPLACE expressions.

edited Jul 01 '14 at 15:59

answered Jul 01 '14 at 15:26

spencer7593

106,611
15
112
140

thank you that works but i've found a simpler solution is to use CONCAT like so "...LIKE CONCAT('%', $term , '%')" thanks for you answer tho nice to see it can be achieved in multiple ways :) – user2886669 Jul 01 '14 at 15:37
@user288669: The expression **`CONCAT('%','soup','%')`** is equivalent to **`'%soup%'`**, so a `LIKE` with either of this is going to return the exact same result set. That's going to match `'soup'` anywhere in title, even within another word. I don't see how the "simpler solution" you found addresses the question you asked. – spencer7593 Jul 01 '14 at 16:03
for some reason this is removing the aditional spaces in my solution :( there are spaces in my solution which somehow makes it work? check this link for the answer http://stackoverflow.com/questions/8537516/how-to-use-an-user-variables-in-mysql-like-clause :) – user2886669 Jul 01 '14 at 16:21
@user2885559: I'm not understanding what you mean by "removing additional spaces". The question you referenced, OP seems to have been expecting (wrongly) that MySQL would "recognize" a user-defined variable within a string literal. That is, the characters '@foo', MySQL would seen as reference to a user-defined variable named `@foo`. In your question, you referenced `$term`, and that's not a MySQL variable either. The form `CONCAT('%',?,'%')` or `CONCAT('%',:foo,'%')` is used for prepared statements. And `CONCAT('%',@foo,'%')` is for user-defined variables. – spencer7593 Jul 01 '14 at 17:51
But none of that really has anything to do with matching only whole words only (avoiding matches to partial words.) And `$term` is not a reference to a MySQL user-defined variable. MySQL user-defined variables start with a **`@`** character, not a **`$`** character. – spencer7593 Jul 01 '14 at 18:07

Jens · Answer 2 · 2014-07-01T15:22:21.803

0

if you use mysql use this Query

SELECT * FROM tbl_recipes WHERE title like '% soup %'

contains is not a mysql function.

edited Jul 01 '14 at 15:22

answered Jul 01 '14 at 15:15

Jens

67,715
15
98
113

3

*"but that matches all records where $term appears anywhere in the title regardless of whether its part of another word or not."* - OP – Tom Jul 01 '14 at 15:17
your edit doesn't work, i thought that was the obvious answer but it never returns any records, even if i run it in phpMyAdmin nothing is returned, although there are definately records in the db which should be returned. – user2886669 Jul 01 '14 at 15:25

Search for records in my db which match/contain certain WHOLE keyword(s)

2 Answers2