Search name by first and last name

1

I'm having a hard time crafting a query MySQL to search for a name, so the person can type the first and last name and accept another name among them. For example:

Search: 'JOSÉ NUNES' or 'NUNES JOSÉ'

Result: JOSÉ SILVA NUNES

The way I search today is basically:

SELECT * FROM pessoas WHERE nome LIKE %pesquisa%

I read something about REGEXP, but I could not use it functionally and I did not want to be repeating several " AND WHERE nome LIKE... " because I use PDO, so it complicates a little.     

asked by anonymous 06.06.2017 / 20:46

3 answers

0

I was able to use RLIKE, like this:

  

SELECT * FROM person WHERE name RLIKE '(? =. * Silva) (? =. * Jose)'

so no matter the order, or if there is something between them: D

    
06.06.2017 / 21:42
0

Try this:

SELECT * FROM pessoas WHERE nome LIKE %$nome%$sobrenome%

Assuming you have separate fields for the first and last name

    
06.06.2017 / 20:52
0

Try to replace the spaces, replacing them with the wildcard:

php:

 str_replace (' ' , '%' , $pesquisa )

mysql

REPLACE(pesquisa,' ','%')

Editing:

use the RLIKE command, but it can return unwanted records. Example:

SELECT * FROM pessoas WHERE nome RLIKE REPLACE('JOSÉ NUNES',' ','|')

This query will return the equivalent of:

SELECT * FROM pessoas WHERE nome LIKE %JOSE% OR nome LIKE %NUNES%
    
06.06.2017 / 20:53