Mysql sort strings like numbers

wordpress meta

title: 'MySQL Sort Strings Like Numbers'
date: '2014-07-10T11:37:00-05:00'
status: publish
permalink: /mysql-sort-strings-like-numbers
author: admin
excerpt: ''
type: post
id: 726
category:
    - mySQL
tag: []
post_format: []

There are a lot of articles out on the web that have suggestions around sorting a string like a number. One quick way with simple strings is "SELECT st FROM table ORDER BY st * 1". For me it was a little tricky because I needed to sort on a field called section and look like this "1.1.1". All fine until you have "1.1.10" and "1.12.1" etc.

For me the following worked.

mysql> SELECT section FROM tasks ORDER BY SUBSTRING_INDEX(section,'.',1),SUBSTRING_INDEX(SUBSTRING_INDEX(section,'.',2),'.',-1) * 1,SUBSTRING_INDEX(section,'.',-1) * 1;