Full text query with a single quote

Question

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?

Yb

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 – Pollute

Answer 1

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)

Misconstruction

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) – Yb

I have to say I haven't used MATCH() ever. I'd use a regular expression instead. – Misconstruction

@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 – Underslung

Answer 2

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)

Egotist

Answer 3

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

Diazonium