PARSENAME System Function To The Rescue
Sorting IP addresses by its numeric value is made easy in SQL Server thanks to
the PARSENAME system function. Technically, the main purpose of the
PARSENAME system function is to return the specified part of an object name,
with the parts of an object being the object name, owner name, database name
and server name. When we say object here, we mean either a table, view or
any SQL Server object.
Since the naming convention of a SQL Server object is quite similar to the
naming convention of an IP address, similar in the sense that they both use a
period (.) to separate each part and both have a maximum of 3 periods, we can
use the PARSENAME system function to get the different parts of an IP address.
One good thing about the PARSENAME system function is that it does not indicate
whether or not an object by the specified name exists. It just returns
the specified part of the given input.
To put the PARSENAME system function into use, the following SELECT statement
will return the IP addresses sorted by its numeric value instead of by the
SELECT [IPAddress] FROM [WorkStation]
ORDER BY CAST(PARSENAME([IPAddress], 4) AS INT),
CAST(PARSENAME([IPAddress], 3) AS INT),
CAST(PARSENAME([IPAddress], 2) AS INT),
CAST(PARSENAME([IPAddress], 1) AS INT)
The output of this SELECT statement is the following, which sorts the IP
addresses as required.