Thread: Accent insensitive search

Hi ! Does anyone knows how to make accent insensitive searches??

For example

i have this data in a table

--------
Colón
Polo
--------

I need that this query

SELECT * FROM testtable WHERE testfield like '%olo%';

brings both results instead of only showing Polo.

Thanks in advance!

On Tuesday 01 July 2003 17:11, Alejandro Javier Pomeraniec wrote:
> Hi ! Does anyone knows how to make accent insensitive searches??
>
> For example
>
> i have this data in a table
>
> --------
> Colón
> Polo
> --------
>
> I need that this query
>
> SELECT * FROM testtable WHERE testfield like '%olo%';
>
> brings both results instead of only showing Polo.

One solution[*]:

SELECT * FROM testtable WHERE to_ascii(testfield,'LATIN1') LIKE '%olo%'

Note this might not work with all database encodings, especially UNICODE.

[*] no doubt someone will be along in a moment with another.


Ian Barwick
barwick@gmx.net

On Tue, Jul 01, 2003 at 03:11:54PM +0000, Alejandro Javier Pomeraniec wrote:

> Hi ! Does anyone knows how to make accent insensitive searches??

Convert both the pattern and the column to ASCII with to_ascii

> SELECT * FROM testtable WHERE testfield like '%olo%';

SELECT * FROM testtable WHERE to_ascii(testfield) like '%olo%';

Do you know the pgsql-ayuda mailing list?  You may like it.
http://tlali.iztacala.unam.mx/mailman/listinfo/pgsql-ayuda

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Pensar que el espectro que vemos es ilusorio no lo despoja de espanto,
sólo le suma el nuevo terror de la locura" (Perelandra, CSLewis)