CODEWITHSUNDEEP
mysqlsortingnatural-sort
VeroLom
VeroLom

Reputation: 3934

mysql natural sorting

I have table like server(id,name,ip). When I'm trying to sort results by name, I get:

srv1,srv10,srv11,srv2,srv6

but I need the results like srv1,srv2,srv6,srv10,srv11

One idea I know is 

ORDER BY LENGTH(name), name

but I have different lengths in name column

What do I need to do?

Upvotes: 2

Views: 2184

Answers (2)

marzapower
marzapower

Reputation: 5611

Natural sorting is not implemented in MySQL. You should try a different approach. In this example I assume that the server name has always the same template (i.e. srv###).

select
    name, 
    mid(name, 4, LENGTH(name)-3) as num, 
    CAST(mid(name, 4, LENGTH(name)-3) AS unsigned) as parsed_num 
from server
order by parsed_num asc;

As I said, this approach is very specific, since you assume that the first 3 characters are to be ignored. This could be misleading and difficult to handle if you change the template.

You could chose to add a column to the table, let's call it prefix in which you set the prefix name for the server (in your example it will be srv for each one). Then you could use:

select
    name,
    prefix,
    mid(name, LENGTH(prefix) + 1, LENGTH(name)-LENGTH(prefix)) as num, 
    CAST(mid(name, LENGTH(prefix) + 1, LENGTH(name)-LENGTH(prefix)) AS unsigned) as parsed_num  
from server
order by parsed_num asc;

obtaining a more robust approach.

Upvotes: 1

Marco
Marco

Reputation: 57593

You could try this:

SELECT id,name,ip,CONVERT(SUBSTRING(name FROM 4),UNSIGNED INTEGER) num
ORDER BY num;

Upvotes: 6

Related Questions