Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2007
    Posts
    56

    Unanswered: How to get the correct result from mysql?

    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. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sql_er View Post
    Can someone please suggest what I can do ...
    sure

    use the BINARY operator

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Posts
    56

    How to get the correct result from mysql?

    Quote Originally Posted by r937 View Post
    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!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sql_er View Post
    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...
    oh, sorry

    then i am lost...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Maybe you could jurry-rig something using MySQL :: MySQL 5.1 Reference Manual :: 11.9 Full-Text Search Functions ???

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •