mysql ordering by string with possible blank entries

I just found a workaround to something I’ve always preferred to do in SQL. I’m using MySQL 5 at work, and I had a query where I would order the entries by a column that is a varchar. Since there was the possibility for this column to be blank, it would display all rows with a blank entry first, and then alphabetically from there.

So, the order would be something like: ‘ ‘, ‘ ‘, ‘1’, ‘2’, ‘3’, ‘A’, ‘B’, ‘C’.

What I really wanted was to display the blank ones last, since I wasn’t interested in those. I poked through the string functions available to see if I could conjure up a hack, and ASCII works great, as it fetches the ASCII numeral of the first character in the string. And, if the string is empty, it will return a zero. And that’s all I needed, was a binary flag to order by first.

Here’s a sample query then:

SELECT string FROM table ORDER BY ! ASCII(string), string;

And the result would be: ‘1’, ‘2’, ‘3’, ‘A’, ‘B’, ‘C’, ‘ ‘, ‘ ‘.

Perfect. :)

About these ads

2 Comments

Filed under Databases

2 responses to “mysql ordering by string with possible blank entries

  1. James Le Cuirot

    Does your data actually look like that? If not, be aware that “Z” will come before “a”. If you’re really not interested in the blanks, do you even want them to be returned? Maybe add a WHERE string != ” condition? Otherwise, maybe you could do something like ORDER BY IF(string = ”, 1, 0), string. I haven’t tried it but it should work.

  2. Steve

    @James,

    The order should be fine on the letters regardless of the case because you’re just checking to see if its zero (empty) or not.

    And yah, I would want to return them because in my case, I’m tacking it on as an order by option. The string column would just be one of many others returned.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s