How to make mysql distinguish between english and non-english characters?

MySQL & MariaDB Other MySQL® Questions
1

Hi,

I ran into the following problem when I select from a table:

SELECT name FROM X WHERE name = ‘Vina Concha y Toro’;

I get 2 results back:

  1. Vina Concha y Toro
  2. Viña Concha y Toro

The 2nd result seems to have a non-english character (i.e. ñ), but mysql does not seem to distinguish between the two results.

I checked the ‘name’ column of table X and the collation is ‘latin1_swedish_ci’. I initially assumed that this was the problem. I then created table Y with collation ‘utf8_general_ci’ for ‘name’ column and inserted both results there [not sure if this was a correct choice, but assumed that utf8 should be a catch all case?]. When I repeated the query, I got the exact same results back. So, this does not seem to have solved the problem.

Can someone please suggest what I can do to only get ‘Vina Concha y Toro’ when I do:

SELECT name FROM X WHERE name = ‘Vina Concha y Toro’;

and only get ‘Viña Concha y Toro’ when I do:

SELECT name FROM X WHERE name = ‘Viña Concha y Toro’;

Thanks a lot!

2

http://dev.mysql.com/doc/refman/5.1/en/string-comparison-fun ctions.html

http://dev.mysql.com/doc/refman/5.1/en/charset-collations.ht ml

There you go. You probably want a binary comparison.