Search with accents and no accents Postgresql

Question

Search with accents and no accents Postgresql

Navigation

#1 by (2 votes)

1

Hello,

I'm trying to do a search through a field in the application, it looks for the word in the database (postgres), however, there is a lot of product registered in the database, with and without an accent, for example, coffee and coffee are registered. .

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

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

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

Just have a problem, he solved the following: Now when I write cafe (without accent) it returns everything to me, even with an accent, OK But when I search for coffee (with an accent) it does not return anything to me, either with or without an accent.

Does anyone have an idea?

java postgresql

asked by anonymous 19.07.2018 / 16:20

1 answer

Set default value when creating a table in SQL Conversion of SESSION MySQL to SESSION in PDO

score 2 · Answer 1

As of version 9.3 you have the module unaccent

To install a module in PostgreSQL, proceed as follows:

1- Verify that the module is available

Select * from pg_available_extensions();

In this case, the unaccent module is distributed with PostgreSQL, then add:

CREATE EXTENSION unaccent;

Now you are able to execute the following query:

Select uncaccent('café');

I've done 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"