MySQL – Case insensitive ORDER BY
MySQL by default will make the ORDER BY option in queries case sensitive. This means that rows with the same starting letters (but different case) may be ordered non-alphabetically. There are three solutions to this. One is to force all database entries to be forced to upper-case or lower-case when inserting. Another is to use the collate option. The other solution is to change the query itself.
Option 2 (Thanks to eremit)
mysql_query(“SELECT * FROM names ORDER BY name COLLATE ‘latin1_general_ci’);
See: MySQL Reference – Case Sensitivity in String Searches and MySQL Reference – Character Set Support
Option 3:
The MySQL query for case insensitive ORDER BY is to add the LOWER() to your field name. For instance:
mysql_query(“SELECT * FROM names ORDER BY lastname”);
will be: mysql_query(“SELECT * FROM names ORDER BY LOWER(lastname)“);
RT @dragonal: MySQL – Case insensitive ORDER BY http://bit.ly/9xGo2r
Generally that’s worth a try, but as far as I remember accents might be still a problem.
Another solution to get an case-insensitive ORDER BY is:
mysql_query(“SELECT * FROM names ORDER BY name COLLATE ‘latin1_general_ci’);
See: MySQL Reference – Case Sensitivity in String Searches and MySQL Reference – Character Set Support
Thanks for bringing up the point about the accents. The COLLATE ‘latin1_general_ci’ is a much better solution. I’ll update the post with your helpful tip.
very nice blog
Hi,
.
Thank you for this article. I used the variant with LOWER(`name`) and works very fine
Leave your response!
Most Viewed
Categories
Blogroll
Tags