ORDER BY and special characters

rivka

I've run into an interesting problem, and I'm wondering if anyone knows the solution...I currently am pulling some rows from a table, and ordering them by a specific column. In that column, are some entries with "special characters" such as ö, û, and á. When mySQL sorts the entries that contain such characters, it doesn't read them as the actual letters, but places them at the end of the order. So, for example:

eaten
egg
enter
essay
ewe
eöl

The final term there, "eöl", should go after "enter", if that makes sense. Is there a way to make mysql understand the special characters differently as just regular vowels, or am I completely out of luck when it comes to ordering these words?

goldbug

Some interesting info that might be related:

http://www.mysql.com/documentation/mysql/bychapter/manual_Charset.html#Charset-Unicode

Note: I 've no idea really if this directly pertains to your issue, but it might. (I'm not too versed in the different character set usages in MySQL)

Also, what happens when you define a column using a localized charset?
http://www.mysql.com/documentation/mysql/bychapter/manual_Charset.html#Charset-column

rivka

Ah, that may be my problem...I'm still on mysql 4.0. Looking at the table in question, I can see the data is being stored as ISO-8859-1. Looks like I'll have to try upgrading to 4.1, and then try storing the data as UTF-8 or UCS-2. Thanks for the tip!