37°F

Aaron Parecki

  • Articles
  • Notes
  • Projects
  • Sorting a query in MySQL ignoring the word "The"

    Sat, Apr 19, 2008 9:50pm -07:00

    When you have a database of books or movies, some of the titles begin with "The." If you do a regular ORDER BY on the table, all the titles that start with "the" get clumped together. One option is running an unsorted query and sorting in PHP, but it would be better to sort at the database level. Here is a query I came up with to do that, using an IF function in MySQL!

    SELECT * FROM movies
    ORDER BY IF(SUBSTRING(title,1,4)="The ",SUBSTRING(title,5),name)
    

    Now create a custom function for that, and it's even easier to use!

    CREATE FUNCTION SORTNAME (name VARCHAR(255)) RETURNS VARCHAR(255)
        RETURN IF( (LCASE(SUBSTRING(name,1,4)) = 'the '), SUBSTRING(name,5), name );
    

    Now you can use it in a query like this:

    SELECT * FROM movies
    ORDER BY SORTNAME(title)
    
    #code #database #MySQL #sorting #SQL
    Sat, Apr 19, 2008 9:50pm -07:00
Posted in /articles

Hi, I'm Aaron Parecki, co-founder of IndieWebCamp. I maintain oauth.net, write and consult about OAuth, and am the editor of several W3C specfications. I record videos for local conferences and help run a podcast studio in Portland.

I wrote 100 songs in 100 days! I've been tracking my location since 2008, and write down everything I eat and drink. I've spoken at conferences around the world about owning your data, OAuth, quantified self, and explained why R is a vowel. Read more.

Follow
  • Okta Developer Advocate
  • IndieWebCamp Founder
  • W3C Editor
  • Stream PDX Co-Founder
  • backpedal.tv

  • W7APK
  • ⭐️ Life Stack
  • All
  • Articles
  • Bookmarks
  • Notes
  • Photos
  • Replies
  • Reviews
  • Sleep
  • Travel
  • Contact
© 1999-2019 by Aaron Parecki. Powered by p3k. This site supports Webmention.
Except where otherwise noted, text content on this site is licensed under a Creative Commons Attribution 3.0 License.
IndieWebCamp Microformats Webmention W3C HTML5 Creative Commons