Hyphen in MATCH AGAINST Fulltext MariaDB

Question

I have DB table PERSON something like below

|ID  |FIRST_NAME    |LAST_NAME|
|1   |Peter-Parkar  |Williams |
|2   |Peter Panatano|Williams |
|3   |Peter-Pant    |Nati     |

I want to search and get the first name result using this query

SELECT ID, CONCAT(FIRST_NAME, ' ', LAST_NAME) as PERSON_NAME 
FROM PERSON 
WHERE MATCH(text) AGAINST ('peter-p*' IN BOOLEAN MODE);

I would like to get the results "Peter-Parkar Williams" and "Peter-Pant Nati", but it is not giving as I expected. How to get that result?

Note: I don't want to use "LIKE" operator in this case, strictly want answers with MATCH AGAINST.

How is `FIRST_NAME` defined in your schema, did you use FULLTEXT — RiggsFolly, Sep 29 '20 at 16:38
It is VARCHAR. This is the most exact query I am using SELECT concat(first_name, ' ', last_name) as person_name WHERE ( MATCH(text) AGAINST ('peter-p*' IN BOOLEAN MODE) ) — Sathya, Sep 29 '20 at 17:15

score 0 · Answer 1 · answered Oct 02 '20 at 20:15

If you need to be that specific about first and last names, you must keep them in separate columns. (Note: There are two-word first names and two-word last names.)

In FULLTEXT, you can possibly change whether - is punctuation or not -- see the specific settings for your version of MySQL/MariaDB and Engine (MyISAM vs InnoDB).

In the columnar approach

WHERE first_name LIKE 'peter-p%'
   with
INDEX(first_name)

is quite efficient.

Another approach to use the speed of a FULLTEXT index for this case:

WHERE MATCH(text) AGAINST ('peter' IN BOOLEAN MODE)
  AND text LIKE '%peter-p%'

That would use the index speed to narrow down the search to rows with "peter", then check to see if "-p" followed it.

Hyphen in MATCH AGAINST Fulltext MariaDB

1 Answers1