How to apply Order By on text with digit entry

Question

Here is my test data (MySQL)

test1
test2
test3
test10
test11
mytest

I want to Order these record by text, and than by following number (if present):

mytest
test1
test2
test3
test10
test11

And When I use Order By, my data is sorted as text, like so:

mytest
test1
test10
test11
test2
test3

Could anyone provide me a solution?

Do you always have number at the end? Have you seen this question http://stackoverflow.com/questions/12097368/mysql-order-by-string-with-numbers ? Here is also good explanation of the way of doing it: http://www.copterlabs.com/blog/natural-sorting-in-mysql/ — Stepashka, Apr 21 '15 at 09:31
Dear Stepashka, Actually not sure that some time i have alphabetic record and some time both alphanumeric+alphabetic so in that case what i will do. Like the bellow. asdasda Faisal Town Gulshan ravi new sector Sector Hills sector1 sector2 sector3 sector4 sector10 sector9 Thanks — Hassan Raza, Apr 21 '15 at 09:43

score 0 · Answer 1 · answered Apr 21 '15 at 10:10

In my query I am dividing alphanumeric values from string values, then combing the result,take a look :

  select temp1.a col from
  (select a from tabl WHERE a not REGEXP '[0-9]' order by length(a)) temp1
  union all 
  select temp2.a  from
  (select a FROM tabl WHERE a REGEXP '[0-9]' order by length(a),a) temp2 ;

How to apply Order By on text with digit entry

1 Answers1