CODEWITHSUNDEEP
mysqlsqlsortingsql-order-bynatural-sort
Saurabh Singh
Saurabh Singh

Reputation: 31

mysql natural sort the given data

I have data in the following format :

AC-1234/12
AC-4321/12
ANA-4532/12
231
IHC-543/12
F-125/12
F-345/12
ds-234/12

I want to do a natural sort on this in mysql.

the result should be as such :

AC-1234/12
AC-4321/12
ANA-4532/12
F-125/12
F-345/12
ds-234/12
231

tried some of the mysql natural sorting tricks but didn't work for me.Suggestions would be greatly appreciated.

I tried this :

select column 
from table_name 
Order By LENGTH(column), column

Upvotes: 1

Views: 1001

Answers (2)

Viko Wijaya
Viko Wijaya

Reputation: 21

i have similar problems, will you guys check mine ?

MySQL Order By Alpha Numeric Not Working

and my sql fiddle

http://www.sqlfiddle.com/#!9/7773a6/1

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

You can use the CASE expression to do this like so:

SELECT column
FROM  Tablenametemp 
ORDER BY
   CASE 
     WHEN column REGEXP '^-?[0-9]+$' THEN 1 --If column is number 
     ELSE 0 
   END, column

Here is a SQL fiddle demo

Upvotes: 2

Related Questions