CODEWITHSUNDEEP
phpmysqlunicodecharacter-encoding
Darth Banana
Darth Banana

Reputation: 363

MySQL search for non-ascii charcter NOT working as intended with LIKE

I have in my MySQL database a table with a varchar field that has utf8_general_ci collation. My problem is that when I do a query like

SELECT title FROM node WHERE title LIKE "%ñ%"

in PHP it returns all the titles that have "n" and "ñ" in them. I tried to do the same query from the terminal in linux and I did not get any results. I don't know if it's from the lack of unicode support in the terminal or not.

How can I filter my query to display results that contain the expression with the exact characters I want? Is it a PHP to MySQL transmission problem? I have PHP 5.3 and MySQL 5.1.

PS:

I know

SELECT title FROM node WHERE title LIKE "%n%"

returns the same results (with "n" and "ñ") as the query above, I don't mind that. I just want to be able to filter expressions with unicode charcatrs that match.

Upvotes: 0

Views: 99

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

By default, MySQL tries to be helpful by treated equivalent characters as equal. This includes accents on characters. There is a section of the documentation devoted to this.

Try using the binary operator:

SELECT title FROM node WHERE title LIKE BINARY '%ñ%'

Upvotes: 1

Related Questions