Search with accents and without accents Postgresql

Question

Hello,

I am trying to search through a field in the application, it searches the word in the database (postgres), however, has a lot of product registered in the bank, with and without accent, for example, has coffee and registered cafe..

I looked for a solution for him to ignore the accents and found this solution:

"function('TRANSLATE'," + part + ",'ÀÁÃãáàÉÈéèÍíÓóÒòÕõÚúÇç','AAAaaaEEeeIiOoOoOoUuCc')";

where part is the variable that contains the word the user searched for.

only that there is a problem, it solved me the following: Now when I write cafe(without accent) it returns to me everything, even with accent, OK But when I search for coffee (with accent) it returns me nothing, neither with and without accent.

Does anyone have any idea?

Answer 1

As of version 9.3 you have the module unaccent

To install a module in Postgresql, do so:

1- Make sure the module is available

Select * from pg_available_extensions();

2- In this case, the module unaccent is distributed with Postgresql, so add:

CREATE EXTENSION unaccent;

Now you are able to execute the following query:

SELECT unaccent('café');

I made an example here (Sqlfiddle does not allow adding extensions)

create table produtos (
    id serial primary key,
    nome varchar(40) NOT NULL
);

insert into produtos (nome) values ('Café'),('Cafe sem acento'),('Pão'),('Leite'),('Açucar'),('Feijão'),('Abóbora');

--Tra
Select id, nome
from produtos
where lower(unaccent(nome)) like '%pa%';-- retorna "Pão"


Select id, nome
from produtos
where unaccent(nome) like '%e%';-- retorna "Café", "Cafe sem acento","Leite","Feijão"


Select id, nome
from produtos
where unaccent(nome) like '%ao%';-- retorna "Pão","Feijão"

In your case, apply the function both in the field and in the criterion

Select id, nome
from produtos
where lower(unaccent(nome)) like '%'|| unaccent('café') || '%';-- retorna "Café","Cafe sem acento"