MYSQL FULLTEXT search does not work with hypen 

Question

Issue: I am using the MYSQL and PHP latest versions. We are facing the issue in the MYSQL FULLTEXT search. It does not work with the hypen(-) and plus(+) characters.

Example:1 In the industry table, 'industry_size' field have the following values:

 1. 1
 2. 2 - 9
 3. 10 - 15

If i am using the search term 2 - 9, it will show the empty result but the correct answer is 2 - 9.

Example:2 In the user table, 'phone_number' field have the following values:

 1. 9856237845
 2. [phone]
 3. [phone]

If i am using the search term +91 89, it will show the empty result but the correct answer is [phone].

Kindly advise. Thanks in advance.

Answer 1

You need to use *. The documentation says:

The asterisk serves as the truncation (or wildcard) operator. Unlike the other operators, it should be appended to the word to be affected. Words match if they begin with the word preceding the * operator.

Try this:

MATCH(field) AGAINST("+91 89*" IN BOOLEAN MODE)

or you can use Binary operator

where BINARY your_column = BINARY "2-9"