Sort IP Addresses
Let's say you have a table of work stations that contain IP addresses, which are stored as VARCHAR data type with length 15, and you want to sort your table by the IP address. The sorting of the IP address must be sorted by the numeric values of the IP address instead of its VARCHAR value.
As an example, let's create the following table and populate it with random IP addresses as follows:
CREATE TABLE [WorkStation] ( [IPAddress] VARCHAR(15) )
INSERT INTO [WorkStation] ( [IPAddress] ) VALUES ('255.255.0.0')
INSERT INTO [WorkStation] ( [IPAddress] ) VALUES ('98.123.251.21')
INSERT INTO [WorkStation] ( [IPAddress] ) VALUES ('192.120.40.243')
INSERT INTO [WorkStation] ( [IPAddress] ) VALUES ('207.46.199.60')
INSERT INTO [WorkStation] ( [IPAddress] ) VALUES ('10.0.0.1')
INSERT INTO [WorkStation] ( [IPAddress] ) VALUES ('68.142.197.0')
INSERT INTO [WorkStation] ( [IPAddress] ) VALUES ('255.255.255.255')
INSERT INTO [WorkStation] ( [IPAddress] ) VALUES ('65.54.152.142')
INSERT INTO [WorkStation] ( [IPAddress] ) VALUES ('64.233.188.15')
INSERT INTO [WorkStation] ( [IPAddress] ) VALUES ('65.97.176.172')
Selecting the records from the table sorted by the IP address will yield the following output:
SELECT [IPAddress] FROM [WorkStation]
ORDER BY [IPAddress]
IPAddress
---------------
10.0.0.1
192.120.40.243
207.46.199.60
255.255.0.0
255.255.255.255
64.233.188.15
65.54.152.142
65.97.176.172
68.142.197.0
98.123.251.21
As can be seen from this output, the IP addresses are sorted by its VARCHAR value instead of its numeric value. The IP address '255.255.255.255' should be the last one listed but instead, it is in the fifth because '2', the first character in the IP address, comes before '6', which is the first character of the next IP address.
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 VARCHAR value:
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.
IPAddress
---------------
10.0.0.1
64.233.188.15
65.54.152.142
65.97.176.172
68.142.197.0
98.123.251.21
192.120.40.243
207.46.199.60
255.255.0.0
255.255.255.255