Re: Sorting results ith umlauts in UTF8 tables
Hello.
Maybe this could help you somehow:
http://bugs.mysql.com/bug.php?id=12191
Check that you are running the latest release.
Nico Grubert [EMAIL PROTECTED] wrote:
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
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sorting results ith umlauts in UTF8 tables
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sorting Results
Hello all, Is there away, within the sql query, to sort the query results in alphabetical order, IE... going from A... to ..Z. This would be like titles of mailing lists. TIA -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(985)902-8484 MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Sorting Results
Cancel this! :) after reading it again, I realized a simple ORDER BY will do the trick nicely... Happy Holidays, Mike(mickako)Blezien wrote: Hello all, Is there away, within the sql query, to sort the query results in alphabetical order, IE... going from A... to ..Z. This would be like titles of mailing lists. TIA -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(985)902-8484 MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Sorting Results
ORDER BY field ASC|DESC --Joe -- Joe Stump [EMAIL PROTECTED] http://www.joestump.net -Original Message- From: Mike(mickako)Blezien [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 12, 2002 11:45 AM To: MySQL List Subject: Sorting Results Hello all, Is there away, within the sql query, to sort the query results in alphabetical order, IE... going from A... to ..Z. This would be like titles of mailing lists. TIA -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(985)902-8484 MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Sorting Results
Mike(mickako)Blezien wrote: Hello all, Is there away, within the sql query, to sort the query results in alphabetical order, IE... going from A... to ..Z. This would be like titles of mailing lists. Well... you mean other than ... ORDER BY 'fieldname' ?? - Csongor - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Sorting Results
ORDER BY col [ASC|DESC] http://www.mysql.com/doc/en/SELECT.html -Original Message- From: Mike(mickako)Blezien [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 12, 2002 11:45 AM To: MySQL List Subject: Sorting Results Hello all, Is there away, within the sql query, to sort the query results in alphabetical order, IE... going from A... to ..Z. This would be like titles of mailing lists. TIA -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(985)902-8484 MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Sorting UK Postcodes (WAS Sorting Results)
Hi, What about if you want to sort UK Postcodes ? They can be E1,E2,..,E12,E13 Order by name would do: E1,E10,E11,E12,E13,E2,E3,E4,... how can I get with a simple ORDER BY query E1,E2,E3,E4,E5,... ? Cheers, Damien COLA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Sorting UK Postcodes (WAS Sorting Results)
Sorry, I forgot to say that postcode can be one or 2 letters in front of the numbers. -Message d'origine- They can be E1,E2,..,E12,E13 Order by name would do: E1,E10,E11,E12,E13,E2,E3,E4,... how can I get with a simple ORDER BY query E1,E2,E3,E4,E5,... ? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Sorting UK Postcodes (WAS Sorting Results)
Damien -- What about: select substring(code, 1, 1) as letter, substring(code, 2, 2) + 0 as number from epost order by 2 +++ | letter | number | +++ | E | 1 | | E | 2 | | E | 3 | | E | 4 | | E | 10 | | E | 11 | | E | 12 | | E | 13 | +++ 8 rows in set (0.00 sec) Then just use your scripting language to join the letter and number back together. I tried mysql concat() but it makes the whole thing into a wrong-sorted string again. FWIW -- Doug At 10:17 PM 12/12/2002 +0100, Alliax wrote: Hi, What about if you want to sort UK Postcodes ? They can be E1,E2,..,E12,E13 Order by name would do: E1,E10,E11,E12,E13,E2,E3,E4,... how can I get with a simple ORDER BY query E1,E2,E3,E4,E5,... ? Cheers, Damien COLA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Sorting UK Postcodes (WAS Sorting Results)
Hi, Could you seperate the letters into one column and the numbers in another... Then you could group by the Alphabetical column, and order by the numerical column... Regards, Chris Stark -Original Message- From: Alliax [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 12, 2002 4:53 PM To: MySQL List Subject: RE: Sorting UK Postcodes (WAS Sorting Results) Sorry, I forgot to say that postcode can be one or 2 letters in front of the numbers. -Message d'origine- They can be E1,E2,..,E12,E13 Order by name would do: E1,E10,E11,E12,E13,E2,E3,E4,... how can I get with a simple ORDER BY query E1,E2,E3,E4,E5,... ? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Sorting UK Postcodes (WAS Sorting Results)
At 22:53 12/12/2002 +0100, Alliax wrote: Sorry, I forgot to say that postcode can be one or 2 letters in front of the numbers. -Message d'origine- They can be E1,E2,..,E12,E13 Order by name would do: E1,E10,E11,E12,E13,E2,E3,E4,... how can I get with a simple ORDER BY query E1,E2,E3,E4,E5,... ? If sorting by number is important, then split them on input and store them in two separate files. But I don't really know why you'd want to sort them numerically anyway - UK postcodes aren't sequential in operation: E1 is not necessarily adjacent to E2 on the map. So having a sequence of E1,E10,E2, etc is no less applicable than E1,E2,E10 would be. Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Sorting UK Postcodes (WAS Sorting Results)
Thank you and Yes, separating the postcodes into field letters and numbers may be the only way to be able to order a sequence like that: CV1,CV2,..,CV10,.. It would also work since there could as many letters as needed Anyone has a simple query for odering sequences correctly ? Cheers, Damien COLA -Message d'origine- Could you seperate the E's in one column and the numbers in another... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Sorting UK Postcodes (WAS Sorting Results)
Hi, yes, that would do, except that I don't know how many letters are in front of the numbers. And since they're not the same letters perhaps ORDER by 1,2 would have done it perfectly, if only we could substring() intelligently first. Cheers, Damien COLA -Message d'origine- SQL: select substring(code, 1, 1) as letter, substring(code, 2, 2) + 0 as number from epost order by 2 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Sorting UK Postcodes (WAS Sorting Results)
Sorting by number is not important, it was more a general question about sequences of variousnameXXX and how to order them correctly with a simple query. Splitting in 2 fields seems the only way then. For the postcode it's just to provide the user with an easy lookup table, but it's not *that* important since the way it's ordered now is the same as many (if not all) file systems do. If sorting by number is important, then split them on input and store them in two separate files. But I don't really know why you'd want to sort them numerically anyway - UK postcodes aren't sequential in operation: E1 is not necessarily adjacent to E2 on the map. So having a sequence of E1,E10,E2, etc is no less applicable than E1,E2,E10 would be. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
