"RuiSMonteiro" <[EMAIL PROTECTED]> wrote on 22/09/2004 09:35:00:
> Hello there,
> I was wondering how I could make a specific type of search when the
> string has more than one word.
> Ex.---------------------------------------------
> String = "Green Apple"
> Select * from fruits
> where
> (fruits.color like '%Green Apple%'
> or fruits.type like '%Green Apple%')
> ------------------------------------------------
> What I thought was breaking the string in 2 words and compares each
> word with the fields. The problem is that I can't control how many
> fields should be compared.
> Also don't know how to compare each word. The following syntax doesn't
work:
> -----------------------------------------------
> Select * from fruits
> where
> (fruits.color like in ('%Green%', '%Apple%')
> or fruits.type like in ('%Green%', '%Apple%')
> -----------------------------------------------
> Any ideas would be very thankful.
I suspect that what you want is a Fulltext index. This splits the text up
into words and does a separate search for the separate words. This is much
more efficient than the LIKE search for the case when you need leading %
characters, because this forces a full linear search. The fulltext search
would look something like
MATCH fruits.color AGAINST ("Green", "Apple")
Unfortunately, Fulltext searches are available only on MyISAM tables.
See http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html
Alec
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
