MySQL – Case insensitive ORDER BY

March 3, 2010 1:37 pm /

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)“);

Posted in: MySQL / Tagged:

5 Thoughts on “MySQL – Case insensitive ORDER BY”

  1. Pingback: Daynah

  2. eremit on March 4, 2010 at 9:20 am said:

    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

  3. admin on March 6, 2010 at 12:12 am said:

    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.

  4. işitme cihazı on March 29, 2010 at 2:04 pm said:

    very nice blog 🙂

  5. CoursesWeb on December 5, 2011 at 9:10 am said:

    Hi,
    Thank you for this article. I used the variant with LOWER(`name`) and works very fine :).

Post Navigation