Natsort in MySQL

August 17, 2010 mike MySQL, 0

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.

Share this Page: