Thread: Accent-insensitive search

Hello, I would like to perform some accent-insensitive searches on my
database, which means that a select query with condition, say, WHERE
NAME = 'HELLÔ' would return records where name is 'HELLO' as well.

My data is encoded in Unicode (UTF8) and therefore I cannot use
Postgre's to_ascii() trick to achieve accent-insensitive searches.

Is there any way I could perform such searches with an UTF8 encoding ?

Thank you.

On Monday 09 July 2007 18:33:49 turbovince wrote:
> Hello, I would like to perform some accent-insensitive searches on my
> database, which means that a select query with condition, say, WHERE
> NAME = 'HELLÔ' would return records where name is 'HELLO' as well.
>
> My data is encoded in Unicode (UTF8) and therefore I cannot use
> Postgre's to_ascii() trick to achieve accent-insensitive searches.
>
> Is there any way I could perform such searches with an UTF8 encoding ?

Check the translate() documentation.  It isn't the best thing in the world to
have to use it and code things by yourself, but it works.

--
Jorge Godoy      <jgodoy@gmail.com>

turbovince escribió:
> Hello, I would like to perform some accent-insensitive searches on my
> database, which means that a select query with condition, say, WHERE
> NAME = 'HELLÔ' would return records where name is 'HELLO' as well.
>
> My data is encoded in Unicode (UTF8) and therefore I cannot use
> Postgre's to_ascii() trick to achieve accent-insensitive searches.

Use the convert() function to turn it into Latin1 (or whatever encoding
you prefer), then to_ascii.

Note that if you have strange chars it will fail anyway (for example
there was this guy not long ago complaining in pgsql-es-ayuda that it
failed when he had the "mu" greek letter in a product description).

--
Alvaro Herrera                         http://www.flickr.com/photos/alvherre/
"El sabio habla porque tiene algo que decir;
el tonto, porque tiene que decir algo" (Platon).