Riaan's SysAdmin Blog

My tips, howtos, gotchas, snippets and stuff. Use at your own risk!


MySQL Sort Strings Like Numbers

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;


Bio Info for Riaan