Hi there,
I am running MySQL 4.1 on Linux.
I have some problems sorting records with german umlauts.
MySQL is configured to have character set "UTF8" as default.
I have created a table like this:
CREATE TABLE tblmembers (
memberid int(11) NOT NULL auto_increment,
lastname varchar(255) NOT NULL default '',
location varchar(255) default NULL,
PRIMARY KEY (memberid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
The, I have inserted some records:
INSERT INTO tblmembers (lastname,location) VALUES ('Ober','Germany');
INSERT INTO tblmembers (lastname,location) VALUES ('Ohm','Germany');
INSERT INTO tblmembers (lastname,location) VALUES ('Öhlz','Germany');
INSERT INTO tblmembers (lastname,location) VALUES ('Öder','Germany');
INSERT INTO tblmembers (lastname,location) VALUES ('Ower','Germany');
Now, I would like get all members whose lastname starts with 'O' (and
also with umlaut 'Ö') sorted by lastname according to german sorting
rules, so my SQL query reads like this:
I tried:
SELECT *
FROM tblmembers
WHERE lastname LIKE 'O%' OR lastname LIKE 'Ö%'
The result reads like this:
Ober
Ohm
Ower
Do I need to covert the 'Ö%' somehow so the records starting with 'Ö'
are also found?
Example 1:
SELECT *
FROM tblmembers
ORDER BY lastname
returns:
Öhlz
Öder
Ober
Ohm
Ower
..which is wrong according to german sorting rules.
Example 2:
SELECT *
FROM tblmembers
ORDER BY lastname
COLLATE utf8_general_ci
returns:
Öhlz
Öder
Ober
Ohm
Ower
The proper sorting order according to german sorting rules is:
Ober
Öder
Ohm
Öhlz
Ower
Any idea how I can sort the results proper?
Nico
