MySQL order varchar alpha-numerically

Question

Given a VARCHAR column called title with the following SELECT title FROM table ORDER BY title results:

Alpha 11
Alpha 2
Beta 1
Beta 11
Beta 2

I would like this to be in the "correct" order of

Alpha 2
Alpha 11
Beta 1
Beta 2
Beta 11

Is there a way to do this?

alter your table to have two columns for that, Field1 which contains *ALPHA*, *BETA*,.. and `Field2` which contains the numeric part. and you can now easily sort it, `ORDER BY Field1 ASC, Field2 ASC` becomes life easier. — John Woo, Jan 19 '13 at 03:34

score 2 · Answer 1 · answered Jan 19 '13 at 03:35

2

Try this:

SELECT title ,
  SUBSTRING_INDEX(title, '', 1) as title_str,
  SUBSTRING_INDEX(title, ' ', -1) + 0 as title_num
FROM table 
ORDER BY title_str, 
  title_num

answered Jan 19 '13 at 03:35

John Conde

217,595
99
455
496

score 1 · Answer 2 · edited May 23 '17 at 12:27

1

This is called Natural Order sorting. Unfortunately there is no such sort algorithm built-in to MySQL, so your most reliable bet would be to create a UDF to do it for you.

However, this question has an interesting answer:

Here is a quick solution:
SELECT title
FROM table
ORDER BY LENGTH(title), title

edited May 23 '17 at 12:27

Community

1
1

answered Jan 19 '13 at 03:37

Niet the Dark Absol

320,036
81
464
592

Saharsh Shah · Answer 3 · 2013-01-19T05:23:50.820

0

Try this:

SELECT title 
FROM tablename 
ORDER BY SUBSTRING_INDEX(title, ' ', 1), 
         CAST(SUBSTRING_INDEX(title, ' ', -1)  AS UNSIGNED);

edited Jan 19 '13 at 05:23

answered Jan 19 '13 at 03:55

Saharsh Shah

28,687
8
48
83

MySQL order varchar alpha-numerically

3 Answers3