Sort MySQL results alphabetically, but with numbers last

mysql sql-order-by alphabetical-sort
Kzqai picture Kzqai · May 2, 2011 · Viewed 22.1k times · Source

Often, sorting is done with symbols sorted to the top, like 0 or * or &. This is the default way that mysql sorts; numbers and symbols and then A-Z. However, that makes the often ugliest or most badly formatted results float to the top (e.g. a result of @#$@3423 or 8 inch or &amp).

So I'd like to do a modified form of that, letters first A-Z, and then special characters last.

How would I go about creating that type of sort? Something in the ORDER BY clause?

Answer

Matthew picture Matthew · May 2, 2011

Based on a google-cached link to this page: http://www.google.com/url?sa=t&source=web&cd=3&ved=0CCUQFjAC&url=http%3A%2F%2Fblog.feedmarker.com%2F2006%2F02%2F01%2Fhow-to-do-natural-alpha-numeric-sort-in-mysql%2F&ei=Zg2_TZyKDaffiALjjqwo&usg=AFQjCNGS-rX7AmfrumXK8J7bVSj96bSSmQ

EDIT: Original link is dead. Here is another link which actually explains what is happening better than the first link did:

http://matthewturland.com/2008/11/05/natural-ordering-in-mysql/

You might try this

SELECT names FROM your_table ORDER BY names + 0 ASC

Related questions

How does MySQL process ORDER BY and LIMIT in a query?

I have a query that looks like this: SELECT article FROM table1 ORDER BY publish_date LIMIT 20 How does ORDER BY work? Will it order all records, then get the first 20, or will it get 20 records and order them by …

Read More
MySQL order by before group by

There are plenty of similar questions to be found on here but I don't think that any answer the question adequately. I'll continue from the current most popular question and use their example if that's alright. The task in this …

Read More
Order a MySQL table by two columns

How do I sort a MySQL table by two columns? What I want are articles sorted by highest ratings first, then most recent date. As an example, this would be a sample output (left # is the rating, then the article …

Read More