[thelist] sql: order by and umlauts/entities

[rudy]

> I have a table containing names which have umlauts saved
> as entities in it, eg. "Köln".
> If I do a "select name order by name" on this table, I get "Köln -
> Kassel" instead of "Kassel - Köln".
>
>This is quite logical, but is there a way using SQL to change this?

hi joxn

sounds to me like it's not a question of the  ö  sorting ahead of the  a
but rather the  &  sorting ahead of the  a

if somehow the entities *are* getting evaluated, then yes, if you control
the server and happen to have an alternate character set lying around, you
can change the sort sequence, but i wouldn't go near something like that

     "10.1.1 The Character Set Used for Data and Sorting
       By default, MySQL uses the ISO-8859-1 (Latin1) character set....
       The character set determines what characters are allowed in
       names and how things are sorted by the ORDER BY and GROUP BY
       clauses of the SELECT statement. You can change the character set
       with the --default-character-set option when you start the server.
http://www.mysql.com/documentation/mysql/bychapter/manual_Server.html#Chara
cter_sets



maybe use a regexp? (i'm guessing, because don't know how to write
those)...

it would have to search the string for each entity and replace it with the
unaccented character for sorting purposes, i.e. replace the six characters
"ö" with the single character "o"

and you'd have to do that for all the entities you're likely to find in the
table


rudy