Reputation: 4430
I want to customise the alphabetical sorting order in my MySQL query.
The Maltese language uses the standard latin alphabet along with the characters: ċ, ġ, ħ and ż. I use the utf8_bin
collation, because ċ is not the same as c (and so on).
This has the result that when I use ORDER BY
, the letters are treated in this order:
a b c d e f g h i j k l m n o p q r s t u v w x y z ċ ġ ħ ż
However I require them to have this order:
a b ċ c d e f ġ g h ħ i j k l m n o p q r s t u v w x y ż z
This means that in ascending order, "abċ" should come before "abz", but currently it comes afterwards, because it is just comparing the binary values of the characters. Is there any way to achieve this custom ordering in MySQL?
I've seen solutions like this which use the ORDER BY FIELD(name, 'a', 'b', 'ċ', ...)
but this does not seem to apply in this case.
I also tried adding a collation to the order function, like so: ORDER BY radicals COLLATE utf8_unicode_ci
, but this essentially treats the accented characters as identical. So, I would get sorted strings like "aċd", "ace", "aċf" but that is also wrong (it should be "aċd", "aċf", "ace").
My fallback plan is of course to do the sorting in PHP, but it would be a lot neater if I could keep the sorting to MySQL itself. Any ideas?
Upvotes: 3
Views: 1599
Reputation: 4430
I have taken Gavin Towey's advice and written such a collation myself. If anyone in the future stumbles across this and is interested, there is a GitHub repository here: Maltese MySQL Collation (utf8_maltese_ci)
Upvotes: 2
Reputation: 3200
In MySQL, the collation defines how sorting and grouping is handled for a character set. If your sorting rules are already defined by a particular language, then the correct collation for you may already exist.
This lists current collations: http://dev.mysql.com/doc/refman/5.5/en/charset-charsets.html
Otherwise, you can add your own custom collation: http://dev.mysql.com/doc/refman/5.5/en/adding-collation.html
Edit: re-reading the question I see that you already have tried various collations -- so you probably just need to add your own custom one.
Upvotes: 3
Reputation: 9822
You can still use FIELD
, but it tests equality between strings.
Since in your case, all arguments are 1-character string, you will have to use ORDER BY FIELD(LEFT(name, 1), 'a', ...)
. It will only sort on the first character though...
Upvotes: 1