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

I do not know the impact of using RegEx, but it seems 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 remove all prefixes, such as street, avenue, av., r. and via, you can still add more things to remove and added the wildcard (sign of % ) at the beginning of LIKE to have other words that it generates something like:

SELECT nome_rua FROM enderecos WHERE nome_rua LIKE '%BATENTE'

You can also do this:

//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%'