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.
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
is quite efficient.
Another approach to use the speed of a
FULLTEXT
index for this case:That would use the index speed to narrow down the search to rows with "peter", then check to see if "-p" followed it.