• PHP
  • Ruby on Rails
  • MySQL
  • Linux
    • SELINUX
    • Fedora
    • debian
  • Apache
  • nginx
  • AJAX
Albertech.net

MySQL – Case insensitive ORDER BY

March 3, 2010 1:37 pm / Albertech.net

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

Share this:

  • Facebook
  • Google
  • Twitter
  • Print
  • Email
Posted in: MySQL / Tagged: mysql case insensitive

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

← Previous Post
Next Post →

Categories

  • AJAX
  • Android
  • Apache
  • Canon Cameras
  • Cloud
  • CMS
  • Computer Mods
  • Conferences
  • Deals
  • debian
  • Fedora
  • Flash
  • Frameworks
  • git
  • Hardware
  • HTML
  • IDE
  • iPhone
  • iPhone App Review
  • jQuery
  • Linux
  • Mac OS X
  • MySQL
  • nginx
  • PHP
  • portfolio
  • Puppet
  • Ruby on Rails
  • Script Reviews
  • SELINUX
  • Software
  • Software Review
  • SQL Server
  • statistics
  • Tech
  • Tomcat
  • Uncategorized
  • VMWARE
  • VPS
  • Windows
  • wordpress
  • Zend Framework

Blogroll

  • DragonAl Flickr
  • Dropbox – Free 2GB Account
  • James' Blog
  • Javascript Compressor
  • PHP Builder Community
  • PHP-Princess.net
  • Rubular – Regular Expression Validator
  • The Scale-Out Blog
  • Tiny MCE

Tags

activation AJAX android antec Apache AWS awstats canon coda codeigniter debian enclosure external free G1 install vmware tools Internet Explorer iphone 5 jquery Linux mx-1 MySQL office 2007 OSX photoshop PHP plugin plugins portfolio redesigned website review rewrite script security SELinux ssh tinymce tutorial upgrade VMWARE vmware server wordpress wordpress mu XSS zend framework
© Copyright 2013 Albertech.net
Infinity Theme by DesignCoral / WordPress
loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.