|
|
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
|