The problem is the encoding: SQL_ASCII encodes only the first 128 characters, so 'ñ' and 'Ñ' cannot be encoded in ASCII. If you insert text 'ñ' or 'Ñ' in a table inside a database (e.g. 'ascii_test') with SQL_ASCII encoding, they will be seen as two distinct characters:
Hexadecimals 'c3', 'b1' and '91' does not decode anything in ASCII, even if terminal show pairs as 'ñ' or 'Ñ'. So ILIKE operator cannot apply case insensitive pattern matching to data which does not encode any string (in the SQL_ASCII encoding) and works as a normal LIKE.
Even if the client which insert data has 8-bit encoding (UTF8, where 'ñ' and 'Ñ' are decoded), SQL_ASCII database cannot convert strings anyway, and problem remains.
The best way is to work with a UTF8 encoded database. Is there a particular reason to work with SQL_ASCII encoding?
no, the only reason until now has been "If it ain't broke, don't fix it."... so maybe it is time to start thinking in migrate the encoding to utf8...
is there any normal procedure to do this?
dump and then restore in a freshly initiated database should be enough?
