Alvaro Herrera wrote: > Martin Langhoff wrote: >> # this is apparently the right way to >> # select base character based on the "equivalence class" >> # as defined in the LC_CTYPE >> =# select * from test where value ~ 'mart[=i=]n'; > > I think it would be much easier if you did something like > > select * from test where lower(to_ascii(value)) = lower(to_ascii('martín')); > > When to_ascii doesn't work (for example because it doesn't work in UTF8) > you may want to use convert() to recode the text to latin1 or latin9.
Well, with the example above to_ascii doesn't work. select to_ascii(value) from test ; ERROR: encoding conversion from UTF8 to ASCII not supported And neither does convert select convert(value using utf8_to_ascii) from test ; ERROR: character 0xc3 of encoding "MULE_INTERNAL" has no equivalent in "SQL_ASCII" select convert('martín' using utf8_to_ascii) from test ; ERROR: character 0xc3 of encoding "MULE_INTERNAL" has no equivalent in "SQL_ASCII" Any other alternatives? BTW, this is on Pg8.2 as seen on Ubuntu Feisty PPC. $ dpkg -l postgresql-8.2 ii postgresql-8.2 8.2.4-0ubuntu0.7.04 cheers, martín -- ----------------------------------------------------------------------- Martin @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St OFFICE: +64(4)916-7224 UK: 0845 868 5733 ext 7224 MOB: +64(21)364-017 Make things as simple as possible, but no simpler - Einstein ----------------------------------------------------------------------- ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match