How to ignore accent in a database search?

Question

How to ignore accent in a database search?

Navigation

#1 by (3 votes)
#2 by (1 votes)

1

I need to search for cities. 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, typing Sao Paulo (without accent), nothing is found because in bank is registered with accentuation.

I've tried the following:

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

Does anyone know how to do this search by ignoring the accent on the bank?

cakephp postgresql

asked by anonymous 16.02.2017 / 18:53

2 answers

3

Use unaccent :

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

link

To install as a super user:

create extension unaccent;

16.02.2017 / 19:53

Break the line after a certain word with PHP How does "DependencyProperty" work?

score 1 · Accepted Answer

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 Paginator I call it like this:

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