In MySQL how to create a select that looks for words "together". For example:
SELECT pessoa FROM tabela
WHERE pessoa like '%josedasilva%'
But this way it does not return anything. Because it is registered as "José da Silva". I would like you to return more records, covering a broader survey, that is, not so specific.
The search is done through a html field via php.
SELECT pessoa FROM tabela
WHERE pessoa like '%jose%' or like '%da%' or like '%silva%'
or
SELECT pessoa FROM tabela
WHERE pessoa like '%jose_da_silva%'
It worked just as well:
select pessoa from tabela where (replace(pessoa, ' ', '')) like replace('%josedasilva%', ' ', '')
How to differentiate "Alex Andre" from "Alexandre"?
The closest you could get is finding people without the space character. But this may involve people who are only first name saved.
select pessoa from tabela where pessoa not like '% %'
Use % to replace characters in search.
SELECT pessoa FROM tabela
WHERE pessoa like '%jose%da%silva%'
In the following scenario, the user wrote jose da silva in the search box. The str_replace() function is used to replace espaço with % :
<?php
$pesquisa = "jose da silva";
$pesquisaSQL = "%" . str_replace(" ", "%", $pesquisa) . "%";
?>
If the user types josedasilva directly into the search, he is doing a wrong search, so ignore this.
user3499898
It is very relative, in the way you said you would need to remove the accents and spaces, this can be done with REPLACE by changing the letters to lowercase.
Here is an example:
SELECT pessoa
FROM tabela
WHERE LOWER(pessoa) = LOWER(REPLACE('josedasilva', ' ', ''))