Sorting in SQL query in ascending order

Question

I have a varchar field CaseNo in table which contains the data in this format for eg.

FA/12/2014,
FA/11/2015, 
FA/12/2015, 
FA/11/2014,  
CC/12/2015,
CC/11/2015

I wanted to sort the result of select query in the following manner

CC/11/2015
CC/12/2015
FA/11/2014
FA/12/2014
FA/11/2015
FA/12/2015

Firstly it should sort first two characters in alphabetic order. And Then the remaining digits in ascending order and result should be like as above..
Note:- last part of the data is year but middle part is not a month it is just a number.
Is this possible to do so.
Thanks

Answer 1

This is probably what you're looking for:

select * from table1 
order by left(col1, 2), convert(date, '01/' + substring(col1, 4, 7), 103) 

My assumption was that the last part is month + year, but of course it could be something else too.