Ordering varchar with numbers in MySQL

Question

I have a table like that:

id | prof
---------
1  | PROF1/2015
2  | PROF2/2015
3  | PROF10/2015

When I make:

SELECT prof FROM table ORDER BY prof ASC

I'm getting result:

PROF1/2015
PROF10/2015
PROF2/2015

But the result should be:

PROF1/2015
PROF2/2015
PROF10/2015

How to make this works?

Dynamic version will require two `SUBSTRING`s and `CAST` while static(for this result only) can achieved with `FIELD(prof, 'PROF1/2015','PROF2/2015','PROF10/2015')`). — potashin, Jan 12 '15 at 11:18
What you are searching for is known as “Natural sorting”, where 10 does follow 9 and not 2. Possible duplicate of [Natural Sort in MySQL](http://stackoverflow.com/questions/153633/natural-sort-in-mysql), See also: http://www.copterlabs.com/blog/natural-sorting-in-mysql/ — feeela, Jan 12 '15 at 11:19
Natural Sort in MySQL works great, but how to count rows `where prof < PROF2/2015` and it counts only id 1, not 1 and 3? — piterek, Jan 12 '15 at 20:55

score 0 · Answer 1 · answered Jan 12 '15 at 11:24

0

Hello try with this i hope this will work for you

SELECT * FROM table ORDER BY (0+prof)>0 asc, (0+prof)=0 asc

answered Jan 12 '15 at 11:24

Vamshi .goli

1 Answers1