&quot;umlaut&quot;s and order by

"umlaut"s and order by

sid

hi... i have a liottle problem with sorting the output of a database.
it is supposed to be a webinterface for university that collects the grades of students, calculates the average grade, etc.

the whole thing is not a problem and pretty much finished except a little problem. the whole thing is in GERMAN. and german names can include 7 more chracters than the englisch alphabet has
these are

ä
Ä
ö
Ö
ü
Ü
ß

the first two (ä, Ä) are usually (in german phone books, etc) treated like A, ö, Ö like O and ü, Ü like a U.

so the name Ortbauer, Ölinger, Oberer would result in an ascending output sorted like this:

select 'lastname' from 'people' order by 'lastname' asc;

lastname
=======
Oberer
Ölinger
Ortbauer

BUT mysql doesnt do that...
if you have the names Ortbauer, Ölinger, Oberer, Bauman, Zeta
it will result in this

select 'lastname' from 'people' order by 'lastname' asc;

lastname
=======
Bauman
Oberer
Ortbauer
Zeta
Ölinger

and this is very unpractical. and it isnt just a problem with names that start wth an umlaut.

so... does anybody know a way to force mysql to sort the result by german rules?

thanks a lot in advance!

nnichols

You need to set the character set -

http://www.mysql.com/doc/en/German_character_set.html

Hope this helps 😉

sid

thanks a lot dude

Sxooter

You should look into using a database with locale and internationalization support built in.

Like Postgresql. If I setup my database like so:

initdb --locale=de_DE

is the output I get.

Unfortunately, using international locales makes postgresql dumb as a brick when it comes to pattern matching, meaning it will have to seq scan for pattern matching stuff, even simple stuff like:

select * from test where name like 'Ö%' will result in a seq scan because the pattern matching is broken right now for international char sets, and not likely to be fixed any time real soon.

On the other hand, it does get the right answer...