How to ignore accentuation in a database search?

Question

I need to conduct a city search. In the database is registered for example, São Paulo, Viamão, Curitiba, but if the user tries to search the city of São Paulo for example by typing Sao Paulo (without accentuation), nothing is found because the bank is registered with accentuation.

I tried the following:

select Municipio.descricao 
  from municipios as Municipio 
where  Municipio.descricao ilike '%Sao Paulo%'

Does anyone know how to do this research ignoring the accent registered in the bank?

Answer 1

Use unaccent:

where unaccent(Municipio.descricao) ilike unaccent('%Sao Paulo%')

https://www.postgresql.org/docs/current/static/unaccent.html

To install as super user:

create extension unaccent;

Answer 2

No cake:

I create the function remove_acento

$this->Cliente->query("create or replace function remove_acento(text)
        returns text as \$BODY$
            select translate($1, 'áàâãäéèêëíìïóòôõöúùûüÁÀÂÃÄÉÈÊËÍÌÏÓÒÔÕÖÚÙÛÜçÇ',
            'aaaaaeeeeiiiooooouuuuAAAAAEEEEIIIOOOOOUUUUcC');
            \$BODY$ LANGUAGE 'sql' IMMUTABLE STRICT;");

Then in the Paginator I call her that:

$conditions[] = "remove_acento(Municipio.descricao) 
           ILIKE remove_acento('" . $filtro4 . "')";