From: Craig Ringer <ringerc@ringerc.id.au>
To: Andreas <maps.on@gmx.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Is there a similarity-function that minds national charsets?
Date: Thu, 21 Jun 2012 10:53:00 +0800
Message-ID: <4FE28C8C.9080300@ringerc.id.au> (raw)
In-Reply-To: <4FE1FAAF.3090004@gmx.net>
References: <4FE1FAAF.3090004@gmx.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ringerc@ringerc.id.au, maps.on@gmx.net
Subject: Re: Is there a similarity-function that minds national charsets?
In-Reply-To: <4FE28C8C.9080300@ringerc.id.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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