pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAccent-insensitive
4+ messages / 3 participants
[nested] [flat]
* Accent-insensitive
@ 2002-12-06 13:30 Pedro Igor <pedroigor@aip.com.br>
2002-12-06 20:33 ` Re: Accent-insensitive Cédric Dufour (public) <cdufour-public@cogito-ergo-soft.com>
0 siblings, 1 reply; 4+ messages in thread
From: Pedro Igor @ 2002-12-06 13:30 UTC (permalink / raw)
To: pgsql-sql
Does have PostgreSQL some option to allow me execute selects accent-insensitive ?
I can´t find any reference, including the manual ....
Pedro Igor
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Accent-insensitive
2002-12-06 13:30 Accent-insensitive Pedro Igor <pedroigor@aip.com.br>
@ 2002-12-06 20:33 ` Cédric Dufour (public) <cdufour-public@cogito-ergo-soft.com>
2002-12-06 22:17 ` Re: Accent-insensitive Joel Burton <joel@joelburton.com>
0 siblings, 1 reply; 4+ messages in thread
From: Cédric Dufour (public) @ 2002-12-06 20:33 UTC (permalink / raw)
To: Pedro Igor <pedroigor@aip.com.br>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Accent-insensitive
2002-12-06 13:30 Accent-insensitive Pedro Igor <pedroigor@aip.com.br>
2002-12-06 20:33 ` Re: Accent-insensitive Cédric Dufour (public) <cdufour-public@cogito-ergo-soft.com>
@ 2002-12-06 22:17 ` Joel Burton <joel@joelburton.com>
2002-12-07 21:19 ` Re: Accent-insensitive Joel Burton <joel@joelburton.com>
0 siblings, 1 reply; 4+ messages in thread
From: Joel Burton @ 2002-12-06 22:17 UTC (permalink / raw)
To: Cédric Dufour (public) <cdufour-public@cogito-ergo-soft.com>; +Cc: Pedro Igor <pedroigor@aip.com.br>; 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@joelburton.com | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Accent-insensitive
2002-12-06 13:30 Accent-insensitive Pedro Igor <pedroigor@aip.com.br>
2002-12-06 20:33 ` Re: Accent-insensitive Cédric Dufour (public) <cdufour-public@cogito-ergo-soft.com>
2002-12-06 22:17 ` Re: Accent-insensitive Joel Burton <joel@joelburton.com>
@ 2002-12-07 21:19 ` Joel Burton <joel@joelburton.com>
0 siblings, 0 replies; 4+ messages in thread
From: Joel Burton @ 2002-12-07 21:19 UTC (permalink / raw)
To: Pedro Igor <pedroigor@aip.com.br>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-12-07 21:19 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-12-06 13:30 Accent-insensitive Pedro Igor <pedroigor@aip.com.br>
2002-12-06 20:33 ` Cédric Dufour (public) <cdufour-public@cogito-ergo-soft.com>
2002-12-06 22:17 ` Joel Burton <joel@joelburton.com>
2002-12-07 21:19 ` Joel Burton <joel@joelburton.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox