0

What method should I use in MySQL to match two or more strings with a column?

I have a column string which contains keywords such as apple, orange, and lemon. What I need to have is to search rows that contains apple and orange using regex, the command has something like this:

where string regexp '(apple|orange)' and fruit = 1

The command above will break the rule, if a row with fruit 1 has only apple it should be not included in the result because fruit 1 didn't have another row which contains orange.

  • Did you really means "another _row_ which contains 'orange'"? Perhaps you are asking whether the _same_ column `string` also contains 'orange'? Example: "We have apples and oranges." should match, but "I bought only apples." should not?? – Rick James Jan 27 '19 at 22:57
  • I am voting to reopen because the [_"dup" Question_](https://stackoverflow.com/questions/9099469/mysql-select-like-or-regexp-to-match-multiple-words-in-one-record) does not address matching "orange and apple". That is, it focuses on "apple" followed by "orange". – Rick James Jan 27 '19 at 23:09

1 Answers1

0

If you want to succeed when (and only when) string contains both "apple" and "orange", then the best way is to have FULLTEXT(string)

WHERE MATCH(string) AGAINST("+apple +orange" IN BOOLEAN MODE)

This will also match "ORANGE colored apples" and a few other variants. Fulltext has some caveats, such as dealing only with "words" and not dealing with short words. But if the restrictions are OK, this will be much faster than LIKE or REGEXP.

If Fulltext will not work, then something like this is best:

WHERE string LIKE '%apple%'
  AND string LIKE '%orange%'

Or it can be done with REGEXP:

WHERE string REGEXP '(apple.*orange)|(orange.*apple)'

If you need to obey word boundaries and/or allow plurals, then add that to your specification; these suggested solutions may need changing. For example, changing .* to .+ would reject "appleorange" while still allowing "apple/orange".

Rick James
  • 135,179
  • 13
  • 127
  • 222