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?
You could try this:
SELECT id,name,ip,CONVERT(SUBSTRING(name FROM 4),UNSIGNED INTEGER) num
ORDER BY num;
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.
prefix, the use the order by prefix asc, parsed_num asc clausehost###, 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.