pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIs there a similarity-function that minds national charsets?
2+ messages / 2 participants
[nested] [flat]
* Is there a similarity-function that minds national charsets?
@ 2012-06-20 16:30 Andreas <maps.on@gmx.net>
0 siblings, 1 reply; 2+ messages in thread
From: Andreas @ 2012-06-20 16:30 UTC (permalink / raw)
To: pgsql-sql
Hi,
Is there a similarity-function that minds national charsets?
Over here we've got some special cases that screw up the results on
similarity().
Our characters: ä, ö, ü, ß
could as well be written as: ae, oe, ue, ss
e.g.
select similarity ( 'Müller', 'Mueller' )
results to: 0.363636
In normal cases everything below 0.5 would be to far apart to be
considered a match.
As it is, I had to transfer the contents of the table into a temporary
table where I translate every ambigous char to it's 2 char representation.
Is there a solution so that detour is not necessary?
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Is there a similarity-function that minds national charsets?
@ 2012-06-21 02:53 Craig Ringer <ringerc@ringerc.id.au>
parent: Andreas <maps.on@gmx.net>
0 siblings, 0 replies; 2+ messages in thread
From: Craig Ringer @ 2012-06-21 02:53 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
On 06/21/2012 12:30 AM, Andreas wrote:
> Hi,
>
> Is there a similarity-function that minds national charsets?
>
> Over here we've got some special cases that screw up the results on
> similarity().
>
> Our characters: ä, ö, ü, ß
> could as well be written as: ae, oe, ue, ss
>
> e.g.
>
> select similarity ( 'Müller', 'Mueller' )
> results to: 0.363636
>
> In normal cases everything below 0.5 would be to far apart to be
> considered a match.
That's not just charset aware, that's looking for awareness of
language-and-dialect specific transliteration rules for representing
accented chars in 7-bit ASCII. My understanding was that these rules and
conventions vary and are specific to each language - or even region.
tsearch2 has big language dictionaries to try to handle some issues like
this (though I don't know about this issue specifically). It's possible
you could extend the tsearch2 dictionaries with synonyms, possibly
algorithmically generated.
If you have what you consider to be an acceptable 1:1 translation rule
you could build a functional index on it and test against that, eg:
CREATE INDEX blah ON thetable ( (flatten_accent(target_column) );
SELECT similarity( flatten_accent('Müller'), target_column );
Note that the flatten_accent function must be IMMUTABLE and can't access
or refer to data in other tables, columns, etc nor SET (GUC) variables
that might change at runtime.
--
Craig Ringer
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2012-06-21 02:53 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-06-20 16:30 Is there a similarity-function that minds national charsets? Andreas <maps.on@gmx.net>
2012-06-21 02:53 ` Craig Ringer <ringerc@ringerc.id.au>
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