Date: Fri, 6 Dec 2002 17:17:33 -0500
From: Joel Burton <joel@joelburton.com>
To: =?iso-8859-1?Q?C=E9dric_Dufour_=28public=29?=
	<cdufour-public@cogito-ergo-soft.com>
Cc: Pedro Igor <pedroigor@aip.com.br>, pgsql-sql@postgresql.org
Subject: Re: Accent-insensitive
Message-ID: <20021206221732.GA22607@temp.joelburton.com>
References: <002401c29d2b$a6e168a0$0b00010a@aip.com.br>
	<007401c29d66$b1374690$3c8addd5@cednetwork.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <007401c29d66$b1374690$3c8addd5@cednetwork.net>
User-Agent: Mutt/1.3.25i

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@joelburton.com  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant