2

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?

Oscar Mederos
  • 29,016
  • 22
  • 84
  • 124
VeroLom
  • 3,856
  • 9
  • 34
  • 48
  • possible duplicate of [Sorting string column containing numbers in SQL?](http://stackoverflow.com/questions/4939518/sorting-string-column-containing-numbers-in-sql) – Shakti Singh May 10 '11 at 09:14
  • I was not found worked code for my issue in that question. – VeroLom May 10 '11 at 10:00

2 Answers2

6

You could try this:

SELECT id,name,ip,CONVERT(SUBSTRING(name FROM 4),UNSIGNED INTEGER) num
ORDER BY num;
Marco
  • 56,740
  • 14
  • 129
  • 152
1

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.

marzapower
  • 5,531
  • 7
  • 38
  • 76
  • Thank you but I names has no single template (it may be host#, sw#, csw# etc.) – VeroLom May 10 '11 at 09:45
  • In that case you should add, for each server, the corresponding prefix in the column `prefix`, the use the `order by prefix asc, parsed_num asc` clause – marzapower May 10 '11 at 09:49
  • If you put in the database different server names (as in `host###`, `sw###`, etc.) the previous answer solution will simply not work, because it will assume that the prefix will always be 3 characters long. Try the approach I suggested, then let me know if it works or not for you. – marzapower May 10 '11 at 09:50