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. 🙂
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.
@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.