Java Developer

MySQL issue with N and Ñ treated as equal value

I encountered and issue today where I have a table of person records. Some last name have values of IBAÑEZ and some have IBANEZ.
For example:


Last NameFirst Name
IBAÑEZArnold
IBAÑEZJohn
IBANEZEdgar
IBANEZRyan
Clearly, there are 2 people whose surname is IBAÑEZ and 2 people with surname IBANEZ. But when I do:
select count(*) from person where last_name like '%IBAÑEZ%';
The result is 4 records.
Apparently, Ñ and N are treated equal. Researching lead me to a stackoverflow article that it is a collation issue.

Either fix it on per SQL instructions:

select last_name, first_name from person where last_name like '%IBAÑEZ%' COLLATE utf8_spanish_ci;
Or alter the table:
ALTER TABLE person CONVERT TO CHARACTER SET utf8 COLLATE utf8_spanish_ci;
And then your simple query will work:
select last_name, first_name from person where last_name like '%IBAÑEZ%';

Tags: collate, mysql, spanish, utf8