How to consult abbreviated words or not without differentiating them, as STREET and R.?

Asked

Viewed 76 times

0

User inserts as search "STREET STOP".
But in the Mysql database you may be saved as "R. Do Stop" or only "STOP".

How to optimize this search to get the best result, or the closest ?

mysql

  • Put an excerpt from your query.

  • Immediately and roughly: Break the informed string and do a search for all parts of it.

  • Search also http://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html

  • try it this way SELECT [listadecampos, separados, por, virgula] FROM lista de tabelas separadas por espaço WHERE campoququerpesquisar LIKE '%palavrabuscadapelousuario%'; that will return everything that is in the field containing the word searched, you can treat the word before generating the query or you can use mysql’s own functionalities, such as UPPER, and others

  • 1

    I voted to leave it open, because the question seems very clear to me, the problem at best is that it should be duplicate, but until then I have not found such a possible duplicate, so I voted by leave open.

1 answer

3


I don’t know what the impact of using Regex, but it looks like a use case, for example:

SELECT nome_rua FROM enderecos WHERE LOWER(nome_rua) REGEXP '(rua\s+|r\.\s+)BATENTE'

Another way you can do is to treat the query string before sending it, for example:

$consulta = 'RUA BATENTE';

$stmt = $mysqli->prepare('SELECT nome_rua FROM enderecos WHERE nome_rua LIKE ?');
$stmt->bind_param('s', $consulta);

//Limpa a consulta
$consulta = '%'.preg_replace('#^(r\.|av\.|rua|avenida|via)\s+#i', '', $consulta);

$stmt->execute();

In this example above using preg_replace, we have removed all prefixes such as street, avenue, av. , r. and via, you can still add more things to remove and added the joker (sign of %) at the beginning of LIKE for they have other words that generates something like:

SELECT nome_rua FROM enderecos WHERE nome_rua LIKE '%BATENTE'

You can do this too:

//Limpa a consulta
$consulta = '%' . preg_replace('#^(r\.|av\.|rua|avenida|via)\s+#i', '', $consulta) . '%';

That generates something like:

SELECT nome_rua FROM enderecos WHERE nome_rua LIKE '%BATENTE%'

Browser other questions tagged mysql

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