MYSQL FULLTEXT search does not work with hypen '-' and plus '+'

Question

Problem: I am using the latest versions of MYSQL and PHP. We are facing a problem in finding MYSQL FULLTEXT. It does not work with hypen (-) and plus (+) characters.

Example: 1 In the industry table, the field "industry_size" has the following meanings:

  eleven
  2.2 - 9
  3.10-15

If I use the search term 2-9, it will show an empty result, but the correct answer 2 - 9 .

Example: 2 In the user table, the "phone_number" field has the following meanings:

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

If I use the search term +91 89 , it will show an empty result, but the correct answer is [phone] .

Please advise. Thanks in advance.

Answer 1

You need to use *. The documentation says:

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

Try the following:

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

or you can use the binary operator

 where BINARY your_column = BINARY "2-9"