My sql sorting varchar field with numeric part

Question

Hi I have a table data_table .I need to get all the title from the table.The title field (type varchar) contain alphanumeric data like below

title
---------
ab 2007
ab 2017
ba 2018
ba 2017
cd 2017
cd 2016

My desired output is

 title
---------
 ba 2018
 ab 2017
 ba 2017
 cd 2017
 cd 2016
 ab 2007

I tried by CAST .But didnt get required result

SELECT title, CAST(title as SIGNED) AS casted_column FROM data_table ORDER BY `title` DESC

sumit · Accepted Answer · 2017-08-21 06:45:38Z

1

Use RIGHT to get last 4 digits and then cast it to unsigned and sort it

SELECT title 
from tbl
order by cast(right(title,4) as unsigned) desc, title desc

answered Aug 21, 2017 at 6:45

sumit

15.5k14 gold badges75 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ScaisEdge · Accepted Answer · 2017-08-21 06:40:14Z

1

if your title end always with 4 digit you could substr for obtain the number and use in sort

      SELECT title
      FROM data_table
      ORDER BY substr(`title`, -4) DESC , title DESC

answered Aug 21, 2017 at 6:40

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges