Approximation mysql filter

Asked

Viewed 120 times

6

I came across some cases where users reported me not in the database, certain clients, I noticed that it is common to abbreviate the middle names or the absence of them, for example:

Name: João Ferreira da Silva

The user when searching:

select * from usuarios where nome like '%João da Silva%';

It would not get results, as well as:

select * from usuarios where nome like '%João F. da Silva%';

I ask you, what is the best way to arrive at these results in an approximate way.

mysql

2 answers

3

2

Replace the spaces by % in the search term before using it in the query. This way you will get names that exist in between.

select * from usuarios where nome like '%João%da%Silva%';

Adding your example with the name of the database registered as "João Ferreira da Silva", this server case for search terms like:

  • João da Silva
  • João F da Silva

It will not, however, serve João F. da Silva. To catch this case could also replace the . by a %, or remove the . complete the search term, but I think you may be getting into very specific cases.

  • it is not a little problematic to bring everyone with 'da' in the name?

  • It will only bring all results with "da" if the search term is only "da". In case of being "João da Silva" will bring only results that are formed "[anything] John [anything] of [anything] Silva [anything]". The % works like wildcard.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.