MySQL order varchar alpha numeric

Question

MySQL order varchar alpha numeric

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

:

Alpha 11
Alpha 2
Beta 1
Beta 11
Beta 2

I would like it to be in the "right" order

Alpha 2
Alpha 11
Beta 1
Beta 2
Beta 11

Is there a way to do this?

+3

sql mysql select sql-order-by

kylex 19 jan. '13 at 3:32

source to share

3 answers

John conde · Answer 1 · 2013-01-19T03:35:59+0000

Try the following:

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

Niet the dark absol · Answer 2 · 2013-01-19T03:37:40+0000

This is called natural order sorting. Unfortunately, MySQL doesn't have such a sorting algorithm, so your safest bet would be to create a UDF to do it for you.

However, this question has an interesting answer:

Here's a quick solution:

SELECT title
FROM table
ORDER BY LENGTH(title), title

Saharsh shah · Answer 3 · 2013-01-19T03:55:11+0000

Try the following:

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

MySQL order varchar alpha numeric

More articles: