Just another php, religion, and politics weblog

Natsort in MySQL

I found this query on CodeWalkers (posted by scaRFhogg), and found it to be extremely useful… so I’m reposting it here for your (and my own) future use :)

SELECT *, CASE WHEN ASCII(RIGHT(%column_name%, 1)) > 47 AND ASCII(RIGHT(%column_name%, 1)) < 58 THEN LPAD(CONCAT(%column_name%, '-'), 5, '0') ELSE LPAD(%column_name%, 5, '0') END AS vsort FROM %table_name% ORDER BY vsort;

Note: you’ll want to replace %column_name% with the ACTUAL column name, and %table_name% with the actual table name.

Facebook Twitter Linkedin Digg Reddit Stumbleupon Tumblr Posterous Email Snailmail

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>