Using Mysql how to create a Select that searches "together" words

Asked

Viewed 152 times

0

In Mysql how to create a select that searches "together" words. For example:

SELECT pessoa FROM tabela 
WHERE pessoa like '%josedasilva%'

But in this way nothing returns. Because it is registered as "José da Silva". I would like you to return more records, covering a broader search, that is, not so specific.

The search is done through an html field via php.

mysql sql select phpmyadmin

  • I believe it is ideal to use the Full-Text Search, instead of LIKE.

  • I find that the search with Full-Text is less extensive. Select pessoa from table WHERE MATCH(person) AGAINST (' josedasilva' IN BOOLEAN MODE)

4 answers

0

user3499898

It is very relative, the way you said you would need to remove the accents and the spaces, this can be done with REPLACE by changing the letters to lowercase. Following example: SELECT pessoa FROM tabela WHERE LOWER(pessoa) = LOWER(REPLACE('josedasilva', ' ', ''))

0

How to differentiate "Alex Andre" from "Alexandre"?

The closest I could get to finding people without the space curl. But that can mean people who are only with their first name saved.

select pessoa from tabela where pessoa not like '% %'

  • If you search for "Alexandre" I would like you to return the two terms if it existed: "Alex Andre" and "Alexandre". A very broad research.

0

Utilize % to replace characters in the 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 function str_replace() is used to replace espaço for %:

<?php
 $pesquisa = "jose da silva"; 
 $pesquisaSQL = "%" . str_replace(" ", "%", $pesquisa) . "%";
?>

If the user writes josedasilva directly in the research, he is doing wrong research, so ignore this fact.

  • The search is done through a field via php. So it is not possible to use this way.

  • Replace spaces by % and add % at the beginning and end

0


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 so well:

select pessoa from tabela where (replace(pessoa, ' ', '')) like replace('%josedasilva%', ' ', '')

  • The search is done through an html field via php. How is it possible to use this way?

  • replace whitespace by _ !!! that last sql works perfectly.

  • REPLACE('Jose da silva',' ','_')

  • This way it worked: select person from table Where (replace(person, ' ', '')) like replace('%josedasilva%', ' ', '')

  • So mark it for other people to see that it worked.

Browser other questions tagged mysql sql select phpmyadmin

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