On 06/21/2012 12:30 AM, Andreas wrote:
> Is there a similarity-function that minds national charsets?
> Over here we've got some special cases that screw up the results on
> Our characters: ä, ö, ü, ß
> could as well be written as: ae, oe, ue, ss
> 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
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.
In response to
pgsql-sql by date
|Next:||From: Rihad||Date: 2012-06-21 17:48:56|
|Subject: Need help building this query|
|Previous:||From: Emi Lu||Date: 2012-06-20 19:45:12|
|Subject: Re: Simple method to format a string|