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


source to share


3 answers


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

      

+2


source


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

      

+1


source


Try the following:

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

      

0


source







All Articles