Search for similar names or with typo - Mysql

Question

I am looking to perform a search in a table containing the name of the products. I created a FULLTEXT to optimize the performance of the queries.

SELECT  * FROM tabela WHERE MATCH (campo) AGAINST ('Picant*Defumad*' IN BOOLEAN MODE);

Upshot:

1 LOMBO DEFUMADO PICANTE FATIADO PRIETO BANDEJA

2 CAFÉ PICANTE

3 BISC TAP SNACK CURCUMA E PAPPRICA DEFUMADA 25G

In this search I already obtained an improvement of not being considered the order that the words were typed, as in the first result presented. But I also need that if the user type "PICATE", it seeks records that are similar as in the case of PICANTE .

I have tried to use other forms of HOW as in the case of IN NATURAL LANGUAGE MODE, but did not solve the problem.

Answer 1

One option is to use REGEXP. Following replicable example:

CREATE DATABASE supermarket;
use supermarket;
CREATE TABLE products (id INT, name VARCHAR(100));
INSERT INTO products (id,name)
VALUES (1,"LOMBO DEFUMADO PICANTE FATIADO PRIETO BANDEJA");
INSERT INTO products (id,name)
VALUES (2,"CAFÉ MOGIANA 250g");
INSERT INTO products (id,name)
VALUES (3,"CAFÉ PICANTE");
INSERT INTO products (id,name)
VALUES (4,"CATCH-UP PICATE");

SELECT *
FROM supermarket.products
WHERE name REGEXP 'PICA(N|)TE'

Returns:

+------+-----------------------------------------------+
| id   | name                                          |
+------+-----------------------------------------------+
|    1 | LOMBO DEFUMADO PICANTE FATIADO PRIETO BANDEJA |
|    3 | CAFÉ PICANTE                                  |
|    4 | CATCH-UP PICATE                               |
+------+-----------------------------------------------+

In this example I used a very simple regex, using the information you gave. But this regular expression can become more complex to cover the cases you want.