Re: LIKE problem with characters 'å' (norwe gian) and 'a' (mysql bug?)

[Kent Larsson]

 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'.

Make sure that your table has:
  charset=utf8
  collation=utf8_norwegian_ci
And that every column ALSO has:
  charset=utf8
  collation=utf8_norwegian_ci

Notice that I am making 'utf8_norwegian_ci' up. I looked for it using my MySQL 
Query Browser but couldn't find it. As I'm from Sweden I've had similar 
problems (åäöÅÄÖ matched åaäÅÄAÖO) and setting as above but using (the 
existing) 'utf8_swedish_ci' worked in my case.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

LIKE problem with characters 'å' (norwe gian) and 'a' (mysql bug?)

[Magne Westlie]

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]