From: | Joel Burton <joel(at)joelburton(dot)com> |
---|---|
To: | Cédric Dufour (public) <cdufour-public(at)cogito-ergo-soft(dot)com> |
Cc: | Pedro Igor <pedroigor(at)aip(dot)com(dot)br>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Accent-insensitive |
Date: | 2002-12-06 22:17:33 |
Message-ID: | 20021206221732.GA22607@temp.joelburton.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Fri, Dec 06, 2002 at 09:33:10PM +0100, Cédric Dufour (public) wrote:
> Use the 'to_ascii' function to convert your string to the "no accent" ASCII
> equivalent before accomplishing your comparison
>
> SELECT foo FROM table WHERE ( to_ascii(foo) ILIKE to_ascii('caractères
> accentués') );
>
> This does not work with all database locale (LATIN1 is OK, but LATIN9 is
> not).
>
> I was actually wondering if this is efficient enough or if there is any more
> efficient method to accomplish this...
I'd think that something like:
CREATE FUNCTION lower_ascii (text) RETURNS text AS '
BEGIN
RETURN lower(to_ascii($1));
END
' language 'plpgsql';
CREATE INDEX table_lower_ascii ON table(lower_ascii(field));
would perform better, since we can now use this index, whereas we
couldn't do this with ILIKE to_ascii(...).
Also, not sure it's a good idea to use ILIKE simply to get
lower-case-matching. If the user string ends with '%', for instance, it
will match everything-starting-with, which is probably not what the user
meant. Better the check against lower().
There might be a better way specifically-oriented toward
de-accentuation; this is just generic PG advice.
- J.
--
Joel BURTON | joel(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-12-06 23:00:24 | Re: SELECT FOR UPDATE locks whole table |
Previous Message | Cédric Dufour (public) | 2002-12-06 20:33:10 | Re: Accent-insensitive |