Dear List,
I get incorrect result when searching for the norwegian character 'å'
using LIKE. I get rows with 'a' in it, and visa versa if I search for
'a', I get results which has 'å' in it in addition to the ones with 'a'.
Example:
CREATE TABLE names (
name VARCHAR(255)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO names VALUES
('Foo'), ('Bar'), ('Båt'), ('Bør'), ('Ære');
Now, searching gives me the following results:
mysql> SELECT * FROM names WHERE name LIKE '%å%';
+------+
| name |
+------+
| Bar |
| Båt |
+------+
mysql> SELECT * FROM names WHERE name LIKE '%a%';
+------+
| name |
+------+
| Bar |
| Båt |
+------+
Searching for strings with other norwegian characters seams to work:
mysql> SELECT * FROM names WHERE name LIKE '%ø%';
+------+
| name |
+------+
| Bør |
+------+
I found that I may use
mysql> SELECT * FROM names WHERE LOWER(name) LIKE BINARY LOWER('%å%');
which returns correct results, but this disables me from letting the
user do case sensitive searches.
Am I doing something wrong or stupid? Could this be a MySQL bug?
How do I know this isn't a problem with other utf-8 characters in other
languages?
I've searched in bug reports, but cannot find this exact problem.
Some additional information that might be useful:
mysql> SELECT VERSION();
+------------------------------+
| VERSION() |
+------------------------------+
| 5.0.45-Debian_1ubuntu3.1-log |
+------------------------------+
mysql> SHOW VARIABLES LIKE '%character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
Thanks,
Magne Westlie
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
