How to ignore accentuation in a database search?

Asked

Viewed 950 times

1

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?

  • ...Where Municipio.Descricao ilike '%Sao Paulo%' collate utf8_general_ci

  • happens this ERROR: collation "utf8_general_ci" for encoding "UTF8" does not exist

  • COLLATE Latin1_general_ci_ai Like '%Sao Paulo%' COLLATE Latin1_general_ci_ai

  • Same thing, from the mistake in COLLATE: ERROR: collation "latin1_general_ci_ai" for encoding "UTF8" does not exist

  • I did a job you solved, I’ll post the answer, but thank you for trying.

  • 2

    @Leocaracciolo This collation is for Mysql, this question is Postgresql

  • 1

    Oh yes, so it didn’t work, thanks @Clodoaldoneto

  • sorry, I didn’t notice :) Clodoaldo Neto

Show 3 more comments

2 answers

4

  • Very good Clodoaldo, I will check with those responsible for the server if it is possible to install this tool and I will make use of it. Thank you for your attendance !

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 . "')";

Browser other questions tagged

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