View Full Version : Search database for two words in a field?

09-03-2006, 12:05 AM
Is this possible?

Say the table is :

ID string
01 apple orange pear
02 apple banana
03 orange banana mango pear

And I want to return only rows containing both 'orange' and 'pear'. Looking at the standard list of operators (http://www.developerfusion.co.uk/show/3998/7/) I'm uncertain. Can you do something like this?

WHERE apple IN string && pear IN string

09-03-2006, 12:35 AM
WHERE MATCH (string) AGAINST ('+orange +pear');

09-03-2006, 12:37 AM
You're getting the IN comparison mixed up. Using IN, you check to see if the column is equal to a value in the IN list:

WHERE string in('apple', 'banana', 'orange')

But the string must equal the value in the list.

You might want to try the LIKE function:

WHERE string like '%apple%'
AND string like '%banana%'

Performance will suffer depending on how huge your tables are.

Ideally you would use fulltext searching.


09-03-2006, 12:39 AM
Mwnciau's syntax is an example of fulltext searching :)

Make sure you create a fulltext index if you go that route.

09-03-2006, 12:40 AM
i hope this get u on your way i think it should work

SELECT * FROM table WHERE string LIKE '%apple%' AND string LIKE '%pear%'

Lee Stevens
09-03-2006, 12:43 AM
mic2100's answer should be right or this:

SELECT * FROM table WHERE string {LIKE '%apple%' AND '%pear%'}

09-03-2006, 01:10 AM
SELECT * FROM table WHERE string {LIKE '%apple%' AND '%pear%'}

That won't work.

09-03-2006, 01:16 AM
Thanks guys. I will go with the FULLTEXT route - my understanding is that indexing slows down the insertion of data, not the extraction, which is fine by me.

Do my fields need to have both INDEX and FULLTEXTapplied to them, or just FULLTEXT?