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’, ‘ ‘, ‘ ‘.