Re: Seaching with and without diacritical marks

on 7/13/04 8:20 AM, Ullrich Ralf at Ullrich(at)iai(dot)spk-berlin(dot)de wrote:

> I have a multilingual portal running on PostgreSQL 7.4.2.
> My clients come from spain,portugal, latin america and germany (mainly).
> The main feature of the site is a search engine that retrieves bibliographic
> data, which is stored in
> my database (unicode!) with diacritical marks (e.g. Panamá,América); when
> users enter their search terms with diacritical marks postgres will find the
> requested records, but if a german user enter Panama or America (without
> diacritical marks), the search fails.
> Is there any extension for postgres that allows for both modes of searching on
> the same data?

I've been wrestling with this issue too - I'm working on an art gallery
database which includes work from a number of French-Canadian artists, plus
a few from other countries where names and image titles typically involve
accents as well.

What I've been tentatively planning to do is to handle it in the PHP
frontend rather than the database itself, by setting up a function with
strtr() (string translate) that would strip out the accents while searching
so that results would come up regardless of whether users entered the right
accent, the wrong accent or no accent at all. The strtr() function allows
you to specify a number of pairs of strings to translate, so I could make up
a list of all the commonly used accented characters and have it translate
all search text with those. I'd apply it to both the search terms entered
and the text found, so that any "a" would match any other "a", regardless of
whether it was really an à, á, ä, â or just plain a (let's see if those
accents show up in anyone's e-mail...). It's kind of the way I'm handling
case sensitivity now.

Lynna

--
Resource Centre Database Coordinator
Gallery 44: www.gallery44.org
Database Project: www.gallery44db.org