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)“);
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
Leave your response!
Most Viewed
Categories
Blogroll
Tags
Categories
Recent Posts
Recent Comments