7

I'm having a problem when I try to do a full text search in boolean mode using a string with a single quote and an asterisk wildcard, i.e. "levi's*": it seems to search also for all words beginning with "s", like "spears", when, as far as I know, the quote should be considered part of the word while two single quotes ('') would be a word separator... but maybe I'm wrong.

Please, look at the example here: http://www.sqlfiddle.com/#!2/3dd3e/2/0 - the second row should't be there

how can I do what I want?

Borgtex
  • 3,235
  • 1
  • 19
  • 28
  • Seeing the same problem in MySql 8. The docs mention single quote in middle should be treated as one word but its indexing both words separately as if the quote was a space instead – stonedauwg Mar 03 '23 at 16:28

3 Answers3

3

I guess you should double quote the string you need to search for if it contains single quotes

Eg: MATCH(value) AGAINST ('"levi\'s"* lacost*' IN BOOLEAN MODE)

Mihai Iorga
  • 39,330
  • 16
  • 106
  • 107
2

this gives you the two rows from your example:

SELECT  *
FROM    ft
WHERE   MATCH(value) AGAINST ('"levi\'s" lacost*' IN BOOLEAN MODE)

In http://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html at the end, it talks about exact matches in double quotes. You then just escape the single quote and you are done.

Using parentheses, you can add the asterisk:

WHERE   MATCH(value) AGAINST ('(levi\'s)* lacost*' IN BOOLEAN MODE)
Bart Friederichs
  • 33,050
  • 15
  • 95
  • 195
  • problem is that none of this queries would find i.e. "levi'strauss", the asterisk after the double quotes doesn't seems to do anything ("lacost"* would not work also) – Borgtex Sep 07 '12 at 10:42
  • I have to say I haven't used `MATCH()` ever. I'd use a regular expression instead. – Bart Friederichs Sep 07 '12 at 11:10
  • @BartFriederichs How can I use multiple match() against() in a query?I tried `WHERE MATCH(column1) AGAINST (chicken) AND MATCH(column2) AGAINST(beef) `.Didn't work.Any idea?Thanks – Coder Jan 29 '15 at 00:39
0

Piggy backing on Bart's comment to handle the single quote and still have the search function as a like, I treated each term separately. So the logic is - if a term has a single quote, wrap it with parenthesis, otherwise leave it. Here is some php code that may help

$term = preg_replace("/[']/", "\'", $term);
$terms = explode(' ',$term);
foreach ($terms as &$t) {
    if (strpos($t, "'")) {
        $t = "(".$t.")";
    }
}
$term = implode(' ',$terms);

my match is AGAINST('$term' IN BOOLEAN MODE

illmatic
  • 43
  • 1
  • 8