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!

sure

use the BINARY operator

Hi, thanks for the advice!

I just did the following, as you suggested:

SELECT BINARY Name FROM Table X
WHERE Name = 'Viña Concha y Toro';

and only got 1 correct result and when I tried with the other string I got 1 correct result as well.

This was run against the original table where each column has collation 'latin1_swedish_ci'.

My question now is - do I have to use BINARY word before the name every time I do the query to make sure mysql returns single correct result or is there a way to change character set/collation at the table/column level so that when I just do regular SELECT with an equal sign, it returns the correct result?

Also, it looks like when I use BINARY, case sensitivity is turned on. That is not preferable. I need to be able to do case insensetive search, but be able to differentiate between english and non-english characters.

Is this possible in mysql?

Thank you very much!

oh, sorry

then i am lost...

Maybe you could jurry-rig something using MySQL :: MySQL 5.1 Reference Manual :: 11.9 Full-Text Search Functions ???

-PatP