From pedroigor@aip.com.br Fri Dec  6 13:39:36 2002
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 08896475921
	for <pgsql-sql@postgresql.org>; Fri,  6 Dec 2002 08:39:36 -0500 (EST)
Received: from server45.aip.com.br (unknown [200.199.192.5])
	by postgresql.org (Postfix) with ESMTP id 3253B4758FE
	for <pgsql-sql@postgresql.org>; Fri,  6 Dec 2002 08:39:34 -0500 (EST)
Received: from PEDRO ([10.1.0.11]) by server45.aip.com.br with SMTP (Microsoft
	Exchange Internet Mail Service Version 5.5.2653.13)
	id X40SX5GQ; Fri, 6 Dec 2002 11:41:33 -0300
Message-ID: <002401c29d2b$a6e168a0$0b00010a@aip.com.br>
From: "Pedro Igor" <pedroigor@aip.com.br>
To: <pgsql-sql@postgresql.org>
Subject: Accent-insensitive
Date: Fri, 6 Dec 2002 11:30:32 -0200
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0021_01C29D1A.E2F0AF10"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 5.00.2919.6700
X-MIMEOLE: Produced By Microsoft MimeOLE V5.00.2919.6700
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200212/71
X-Sequence-Number: 10659

This is a multi-part message in MIME format.

------=_NextPart_000_0021_01C29D1A.E2F0AF10
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Does have PostgreSQL some option to allow me execute selects accent-insensi=
tive ?
I can=B4t find any reference, including the manual ....

Pedro Igor

------=_NextPart_000_0021_01C29D1A.E2F0AF10
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.2920.0" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Does have PostgreSQL some option&nbsp;to a=
llow me=20
execute selects&nbsp;accent-insensitive ?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>I can=B4t find any reference, including th=
e manual=20
....</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Pedro Igor</FONT></DIV></BODY></HTML>

------=_NextPart_000_0021_01C29D1A.E2F0AF10--




From cdufour-public@cogito-ergo-soft.com Fri Dec  6 20:34:32 2002
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 2A5AD475CE5
	for <pgsql-sql@postgresql.org>; Fri,  6 Dec 2002 15:34:32 -0500 (EST)
Received: from aristote2.infomaniak.ch (aristote2.infomaniak.ch
	[212.23.249.227])
	by postgresql.org (Postfix) with ESMTP id 42C264769B4
	for <pgsql-sql@postgresql.org>; Fri,  6 Dec 2002 15:33:15 -0500 (EST)
Received: from cedserver (unverified [213.221.138.60]) by
	aristote2.infomaniak.ch (Rockliffe SMTPRA 4.5.6) with ESMTP id
	<B0026350608@aristote2.infomaniak.ch>;
	Fri, 6 Dec 2002 21:31:12 +0100
From: =?iso-8859-1?Q?C=E9dric_Dufour_=28public=29?=
	<cdufour-public@cogito-ergo-soft.com>
To: "Pedro Igor" <pedroigor@aip.com.br>, <pgsql-sql@postgresql.org>
Subject: Re: Accent-insensitive
Date: Fri, 6 Dec 2002 21:33:10 +0100
Message-ID: <007401c29d66$b1374690$3c8addd5@cednetwork.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0075_01C29D6F.12FD3530"
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook IMO, Build 9.0.6604 (9.0.2911.0)
In-Reply-To: <002401c29d2b$a6e168a0$0b00010a@aip.com.br>
Importance: Normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200212/81
X-Sequence-Number: 10669

This is a multi-part message in MIME format.

------=_NextPart_000_0075_01C29D6F.12FD3530
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 8bit

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...

    Cédric D.
  -----Original Message-----
  From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Pedro Igor
  Sent: Friday, December 06, 2002 14:31
  To: pgsql-sql@postgresql.org
  Subject: [SQL] Accent-insensitive


  Does have PostgreSQL some option to allow me execute selects
accent-insensitive ?
  I can´t find any reference, including the manual ....

  Pedro Igor

------=_NextPart_000_0075_01C29D6F.12FD3530
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2719.2200" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><SPAN class=3D122192820-06122002><FONT face=3D"Courier New" color=3D#0=
000ff=20
size=3D2>Use the 'to_ascii' function to convert your string to the "no acce=
nt"=20
ASCII equivalent before accomplishing your comparison</FONT></SPAN></DIV>
<DIV><SPAN class=3D122192820-06122002><FONT face=3D"Courier New" color=3D#0=
000ff=20
size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D122192820-06122002><FONT face=3D"Courier New" color=3D#0=
000ff=20
size=3D2>SELECT foo FROM table WHERE ( to_ascii(foo) ILIKE to_ascii('caract=
=E8res=20
accentu=E9s') );</FONT></SPAN></DIV>
<DIV><SPAN class=3D122192820-06122002><FONT face=3D"Courier New" color=3D#0=
000ff=20
size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D122192820-06122002><FONT face=3D"Courier New" color=3D#0=
000ff=20
size=3D2>This does not work with all database locale (LATIN1 is OK, but LAT=
IN9 is=20
not).</FONT></SPAN></DIV>
<DIV><SPAN class=3D122192820-06122002><FONT face=3D"Courier New" color=3D#0=
000ff=20
size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D122192820-06122002><FONT face=3D"Courier New" color=3D#0=
000ff=20
size=3D2>I was actually wondering if this is efficient enough or if there i=
s any=20
more efficient method to accomplish this...</FONT></SPAN></DIV>
<DIV><SPAN class=3D122192820-06122002><FONT face=3D"Courier New" color=3D#0=
000ff=20
size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D122192820-06122002>&nbsp;&nbsp;&nbsp; <FONT face=3D"Cour=
ier New"=20
color=3D#0000ff size=3D2>C=E9dric D.</FONT></SPAN></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #0000ff 2px soli=
d; MARGIN-RIGHT: 0px">
  <DIV class=3DOutlookMessageHeader dir=3Dltr align=3Dleft><FONT face=3DTah=
oma=20
  size=3D2>-----Original Message-----<BR><B>From:</B>=20
  pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]<B>=
On=20
  Behalf Of </B>Pedro Igor<BR><B>Sent:</B> Friday, December 06, 2002=20
  14:31<BR><B>To:</B> pgsql-sql@postgresql.org<BR><B>Subject:</B> [SQL]=20
  Accent-insensitive<BR><BR></FONT></DIV>
  <DIV><FONT face=3DArial size=3D2>Does have PostgreSQL some option&nbsp;to=
 allow me=20
  execute selects&nbsp;accent-insensitive ?</FONT></DIV>
  <DIV><FONT face=3DArial size=3D2>I can=B4t find any reference, including =
the manual=20
  ....</FONT></DIV>
  <DIV>&nbsp;</DIV>
  <DIV><FONT face=3DArial size=3D2>Pedro Igor</FONT></DIV></BLOCKQUOTE></BO=
DY></HTML>

------=_NextPart_000_0075_01C29D6F.12FD3530--





From joel@joelburton.com Fri Dec  6 22:17:32 2002
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 073FA47607D
	for <pgsql-sql@postgresql.org>; Fri,  6 Dec 2002 17:17:32 -0500 (EST)
Received: from temp.joelburton.com (65.eebed1.client.atlantech.net
	[209.190.238.101])
	by postgresql.org (Postfix) with ESMTP id 7BEEC475FBF
	for <pgsql-sql@postgresql.org>; Fri,  6 Dec 2002 17:17:31 -0500 (EST)
Received: by temp.joelburton.com (Postfix, from userid 501)
	id 121FE2B9F7; Fri,  6 Dec 2002 17:17:33 -0500 (EST)
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
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200212/82
X-Sequence-Number: 10670

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



From joel@joelburton.com Sat Dec  7 21:19:48 2002
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 380CD4758F1
	for <pgsql-sql@postgresql.org>; Sat,  7 Dec 2002 16:19:48 -0500 (EST)
Received: from temp.joelburton.com (65.eebed1.client.atlantech.net
	[209.190.238.101])
	by postgresql.org (Postfix) with ESMTP id 960E04758E6
	for <pgsql-sql@postgresql.org>; Sat,  7 Dec 2002 16:19:47 -0500 (EST)
Received: by temp.joelburton.com (Postfix, from userid 501)
	id BEA682B9F7; Sat,  7 Dec 2002 16:19:48 -0500 (EST)
Date: Sat, 7 Dec 2002 16:19:48 -0500
From: Joel Burton <joel@joelburton.com>
To: Pedro Igor <pedroigor@aip.com.br>
Cc: pgsql-sql@postgresql.org
Subject: Re: Accent-insensitive
Message-ID: <20021207211948.GA5760@temp.joelburton.com>
References: <002401c29d2b$a6e168a0$0b00010a@aip.com.br>
	<007401c29d66$b1374690$3c8addd5@cednetwork.net>
	<20021206221732.GA22607@temp.joelburton.com>
	<002201c29e3c$f2e2c750$0200a8c0@micro02>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <002201c29e3c$f2e2c750$0200a8c0@micro02>
User-Agent: Mutt/1.3.25i
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200212/90
X-Sequence-Number: 10678

On Sat, Dec 07, 2002 at 07:06:45PM -0300, Pedro Igor wrote:
> Thanks, you know if some possible release would have some internal to deal
> with this ?
> 
> Abraços
> Pedro Igor
> >
> > 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));

Pedro --

Please keep conversations on the list -- other people may know things I
don't (actually, they certainly will!), and it allows other people to
follow the conversation.

As for this being internal, I have no idea -- you could submit it as a
suggestion.

Given how easy it is to implement in plpgsql, I suspect this kind of thing will
stay out of the internals. Keep in mind that if you define this function
in your template1 database, you can have it created automatically in all
new databases you create, so it's one less thing to worry about.

HTH.

-- 

Joel BURTON  |  joel@joelburton.com  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant