Kijan pou m chèche tèks nan yon kolòn san pwoblèm aksan (é, è, ò)?

Question

Lè yon moun chèche `kreyol`, mwen vle li jwenn `Kreyòl` tou. `WHERE non ILIKE '%kreyol%'` pa jwenn li. Kijan pou m fè?

Accepted Answer

Nan PostgreSQL, itilize ekstansyon **`unaccent`** ki retire aksan yo:

```sql
CREATE EXTENSION IF NOT EXISTS unaccent;

SELECT * FROM atik
WHERE unaccent(lower(tit)) LIKE '%' || unaccent(lower('kreyol')) || '%';
```

Pou li rapid sou anpil liy, kreye yon fonksyon `IMMUTABLE` epi yon index trigram:

```sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE FUNCTION f_unaccent(text) RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE
  AS $$ SELECT public.unaccent('public.unaccent', $1) $$;

CREATE INDEX atik_tit_trgm ON atik USING gin (f_unaccent(lower(tit)) gin_trgm_ops);

SELECT * FROM atik WHERE f_unaccent(lower(tit)) LIKE '%kreyol%';
```

Pou yon vrè motè rechèch (plizyè mo, klasman), gade **full-text search** PostgreSQL ak konfigirasyon `simple` + `unaccent`. Se sa ki pi adapte pou kreyòl, paske pa gen diksyonè kreyòl nan Postgres.

2 réponses