Re: [SQL] Internationalisation: SELECT str (ignoring Umlauts/Accents)

> Oh well, let me extend the question then, what I am looking for is a
> solution that works for Switzerland, e.g. a country with 4 official
> languages (one of which basically gets ignored) and a 5th "major"
> language. So, I need a search function to look for German, French, Italian
> and English names (I am not doing Rumantsch [the 4th official language in
> Switzerland], as I don't know anything about the language except for that
> only a few thousand people in Switzerland are left actually using it).

  Ok, I've done it...

  This is a C function, which takes a text argument, and returns a text
argument. It is created as :

  CREATE FUNCTION accents( text )
    RETURNS text
    AS '/usr/local/pgsql/lib/accents.so'
    LANGUAGE 'c';

  And you can use it this way, for example :

  SELECT * FROM town WHERE town_name ~* (SELECT accents('xxxxx') );

  For example, a typical use gives :

patrice=> select accents( 'zurich' );
accents
------------------------------
z[uúÚùÙûÛüÜ]r[iíÍìÌîÎïÏ][cçÇ]h
(1 row)

patrice=> select accents( 'GENEVE' );
accents
------------------------------------
G[eéÉèÈêÊëË]N[eéÉèÈêÊëË]V[eéÉèÈêÊëË]
(1 row)

patrice=> select accents( 'munchen' );
accents
-------------------------------
m[uúÚùÙûÛüÜ]n[cçÇ]h[eéÉèÈêÊëË]n
(1 row)

patrice=> select accents( 'koeln' );
accents
-------------------
k(oe\|ö\|Ö\|ø\|Ø)ln
(1 row)


  I don't put the function itself in an attachment, since it was
considered bad, some time ago. So you can fetch the tar.gz at this
place later in the day :

  http://www.idf.net/patrice/programmes/accents.tar.gz


  I would like other people to look at it also. If there is interest,
I will make it nicer, maybe to put it under contrib.


Hope it helps

Patrice

--
Patrice HÉDÉ --------------------------------- patrice@idf.net -----
Nous sommes au monde. [...] La croyance en un esprit absolu ou en un
monde en soi détaché de nous n'est qu'une rationalisation  de  cette
foi primordiale.  --- Merleau-Ponty, Phénoménologie de la Perception
----- http://www.idf.net/patrice/ ----------------------------------