Thread

1. Getting rid of accents..

   Rajesh Kumar Mallah <mallah@trade-india.com> — 2003-05-27T20:55:06Z

   
   Is there any easy way for converting accented text to
   closest text  without accents in postgresql ?
   
   eg:
   
   BÂLÂ MORGHÂB  to  BALA MORGHAB
   
   
   
   
   Regds
   Mallah.
   
   
   -----------------------------------------
   Get your free web based email at trade-india.com.
      "India's Leading B2B eMarketplace.!"
   http://www.trade-india.com/
   
   
   
   

2. Re: Getting rid of accents..

   Randall Lucas <rlucas@tercent.net> — 2003-05-27T21:48:02Z

   Hi Mallah,
   
   I had this problem once, and put together this bunch of regexes.  It's 
   by no means optimal, but should solve 90% and would easily be adapted 
   into a plperl function.
   
   Begin perl:
      $value =~ s/[\xc0-\xc6]/A/g;
      $value =~ s/[\xc7]/C/g;
      $value =~ s/[\xc8-\xcb]/E/g;
      $value =~ s/[\xcc-\xcf]/I/g;
      $value =~ s/[\xd1]/N/g;
      $value =~ s/[\xd2-\xd6\xd8]/O/g;
      $value =~ s/[\xd9-\xdc]/U/g;
      $value =~ s/[\xdd]/Y/g;
   
      $value =~ s/[\xe0-\xe6]/a/g;
      $value =~ s/[\xe7]/c/g;
      $value =~ s/[\xe8-\xeb]/e/g;
      $value =~ s/[\xec-\xef]/i/g;
      $value =~ s/[\xf1]/n/g;
      $value =~ s/[\xf2-\xf6\xd8]/o/g;
      $value =~ s/[\xf9-\xfc]/u/g;
      $value =~ s/[\xfd\xff]/y/g;
   
   
   
   On Tuesday, May 27, 2003, at 04:55 PM, <mallah@trade-india.com> wrote:
   
   >
   >
   > Is there any easy way for converting accented text to
   > closest text  without accents in postgresql ?
   >
   > eg:
   >
   > BÂLÂ MORGHÂB  to  BALA MORGHAB
   >
   >
   >
   >
   > Regds
   > Mallah.
   >
   >
   > -----------------------------------------
   > Get your free web based email at trade-india.com.
   >    "India's Leading B2B eMarketplace.!"
   > http://www.trade-india.com/
   >
   >
   >
   > ---------------------------(end of 
   > broadcast)---------------------------
   > TIP 3: if posting/reading through Usenet, please send an appropriate
   > subscribe-nomail command to majordomo@postgresql.org so that your
   > message can get through to the mailing list cleanly
   >
   
   
   

3. Re: Getting rid of accents..

   Randall Lucas <rlucas@tercent.net> — 2003-05-27T21:52:02Z

   Full disclosure on previously posted Perl code: I think I may have 
   cribbed all or part of that previous code from something (Perl 
   cookbook?).  In any case, the issue is essentially a mapping of which 
   ascii codes "look like" low-ascii, so I don't think there are any 
   authorship issues.
   
   Best,
   
   Randall
   
   On Tuesday, May 27, 2003, at 04:55 PM, <mallah@trade-india.com> wrote:
   
   >
   >
   > Is there any easy way for converting accented text to
   > closest text  without accents in postgresql ?
   >
   > eg:
   >
   > BÂLÂ MORGHÂB  to  BALA MORGHAB
   >
   >
   >
   >
   > Regds
   > Mallah.
   >
   >
   > -----------------------------------------
   > Get your free web based email at trade-india.com.
   >    "India's Leading B2B eMarketplace.!"
   > http://www.trade-india.com/
   >
   >
   >
   > ---------------------------(end of 
   > broadcast)---------------------------
   > TIP 3: if posting/reading through Usenet, please send an appropriate
   > subscribe-nomail command to majordomo@postgresql.org so that your
   > message can get through to the mailing list cleanly
   >
   
   
   

4. Re: Getting rid of accents..

   Ian Barwick <barwick@gmx.net> — 2003-05-27T22:15:02Z

   On Tuesday 27 May 2003 22:55, mallah@trade-india.com wrote:
   > Is there any easy way for converting accented text to
   > closest text  without accents in postgresql ?
   >
   > eg:
   >
   > BÂLÂ MORGHÂB  to  BALA MORGHAB
   
   Have you looked at to_ascii()? Something along the lines of
   
   select to_ascii('Â', 'LATIN1')
   
   
   Ian Barwick
   barwick@gmx.net
   
   
   

5. Re: Getting rid of accents..

   Rajesh Kumar Mallah <mallah@trade-india.com> — 2003-05-27T23:57:54Z

   Thanks  Ian ,
   
   Indeed its the simplest/easiest solution to this problem i feel.
   i did tried to_ascii function but was not specifying 'LATIN1'
   
   do i was getting error
   
   tradein_clients=# SELECT to_ascii('BÂLÂ MORGHÂB' );
   ERROR:  pg_to_ascii(): unsupported encoding from SQL_ASCII
   
   would u like to explain it ?
   
   Then i created a new database in latin1 encoding loaded the data
   used to_ascii to convert , copied the data to new file and reloaded
   back to original database  ;-)  .oO (tiring )
   
   Lucas Thanks for that perl stuff too i will use it in some program
   that stuffs arbitary text into database.
   
   Regds
   Mallah.
   
   > On Tuesday 27 May 2003 22:55, mallah@trade-india.com wrote:
   >> Is there any easy way for converting accented text to
   >> closest text  without accents in postgresql ?
   >>
   >> eg:
   >>
   >> BÂLÂ MORGHÂB  to  BALA MORGHAB
   >
   > Have you looked at to_ascii()? Something along the lines of
   >
   > select to_ascii('Â', 'LATIN1')
   >
   >
   > Ian Barwick
   > barwick@gmx.net
   >
   >
   > ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off
   > all lists at once with the unregister command
   >    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
   
   
   
   -----------------------------------------
   Get your free web based email at trade-india.com.
      "India's Leading B2B eMarketplace.!"
   http://www.trade-india.com/
   
   
   
   

6. Re: Getting rid of accents..

   Jean-Luc Lachance <jllachan@nsd.ca> — 2003-05-28T14:05:36Z

   Have a look at translate().  It behaves like the unix command 'tr'.
   
   
   Randall Lucas wrote:
   > 
   > Hi Mallah,
   > 
   > I had this problem once, and put together this bunch of regexes.  It's
   > by no means optimal, but should solve 90% and would easily be adapted
   > into a plperl function.
   > 
   > Begin perl:
   >    $value =~ s/[\xc0-\xc6]/A/g;
   >    $value =~ s/[\xc7]/C/g;
   >    $value =~ s/[\xc8-\xcb]/E/g;
   >    $value =~ s/[\xcc-\xcf]/I/g;
   >    $value =~ s/[\xd1]/N/g;
   >    $value =~ s/[\xd2-\xd6\xd8]/O/g;
   >    $value =~ s/[\xd9-\xdc]/U/g;
   >    $value =~ s/[\xdd]/Y/g;
   > 
   >    $value =~ s/[\xe0-\xe6]/a/g;
   >    $value =~ s/[\xe7]/c/g;
   >    $value =~ s/[\xe8-\xeb]/e/g;
   >    $value =~ s/[\xec-\xef]/i/g;
   >    $value =~ s/[\xf1]/n/g;
   >    $value =~ s/[\xf2-\xf6\xd8]/o/g;
   >    $value =~ s/[\xf9-\xfc]/u/g;
   >    $value =~ s/[\xfd\xff]/y/g;
   > 
   > On Tuesday, May 27, 2003, at 04:55 PM, <mallah@trade-india.com> wrote:
   > 
   > >
   > >
   > > Is there any easy way for converting accented text to
   > > closest text  without accents in postgresql ?
   > >
   > > eg:
   > >
   > > BÂLÂ MORGHÂB  to  BALA MORGHAB
   > >
   > >
   > >
   > >
   > > Regds
   > > Mallah.
   > >
   > >
   > > -----------------------------------------
   > > Get your free web based email at trade-india.com.
   > >    "India's Leading B2B eMarketplace.!"
   > > http://www.trade-india.com/
   > >
   > >
   > >
   > > ---------------------------(end of
   > > broadcast)---------------------------
   > > TIP 3: if posting/reading through Usenet, please send an appropriate
   > > subscribe-nomail command to majordomo@postgresql.org so that your
   > > message can get through to the mailing list cleanly
   > >
   > 
   > ---------------------------(end of broadcast)---------------------------
   > TIP 2: you can get off all lists at once with the unregister command
   >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)