Outside the Box: Calculating IP Patterns
In an effort to further simplify the solution, I came up with a less-intuitive approach that you might call outside-the-box thinking. Create a table with all possible patterns of IP addresses, each with the four pairs of arguments required for the SUBSTRING() function (the start of each octet and the length of each octet).
Essentially, any IP address follows the pattern <1–3 characters>.<1-3 characters>.<1-3 characters>.<1-3 characters> . You'll use the LIKE predicate to match an IP address to a pattern that contains underscores to represent the digits. For example, the IP address 192.168.11.10 follows the pattern '___.___.__.__'. You can calculate the starting positions and lengths of all octets in the pattern by counting the number of characters preceding each octet and the number of characters in each octet. In the previous IP address, if we use sn to represent the starting position of nth octet and ln to represent the length of nth octet, the starting position and length values are s1=1, l1=3, s2=5, l2=3, s3=9, l3=2, s4=12, l4=2. After you match the IP address with its pattern, you can use the start position and length values accompanying the pattern as arguments for the SUBSTRING() functions.
You can manually populate such a table with all possible patterns and SUBSTRING() arguments. In total, you have 34 patterns, resulting in 81 rows. Alternatively, you can create a view that returns all possible patterns by cross-joining four instances of an auxiliary table of numbers, filtering for only the numbers 1 through 3 in each instance to represent the possible octet lengths.
First, use the following code to create the auxiliary table of numbers and populate it with at least three values:
CREATE TABLE Nums(n int NOT NULL PRIMARY KEY);
INSERT INTO Nums VALUES(1);
INSERT INTO Nums VALUES(2);
INSERT INTO Nums VALUES(3);
Run the code that Listing 4 shows to create the IPPatterns view, which returns all 81 possible IP patterns along with the start and length values for all four octets for each pattern. The view's query cross-joins four instances of the Nums table (N1, N2, N3, and N4), filtering in each the n values that are less than or equal to 3. In the result of the cross join, the four n values for the four instances of Nums represent all possible combinations of octet lengths.
The SELECT list constructs the actual IP address pattern by replicating underscores for each octet and concatenating the underscores with dots between the octets. The SELECT list also calculates the start positions of the octets by summarizing the lengths of the preceding octets and the number of preceding dots plus 1. The lengths of the octets are simply the n values from the corresponding Nums instance.
Run the following query to see the patterns and arguments that the IPPatterns view returned:
SELECT * FROM IPPatterns;
Table 1 shows the abbreviated result. Having the IPPatterns view (or table) in place lets you write an extremely simple query to get the desired result, as Listing 5 shows. The query joins the base table to the IPPatterns view based on a match between the IP address and the pattern that it follows. The query's ORDER BY clause has four SUBSTRING() functions, each of which extracts an octet according to the start and length arguments that the IPPatterns view provides. The code converts each octet string to a tinyint data type to obtain the correct sort.
Solution That Uses PARSENAME()
All the previous solutions use standard SQL constructs, so they're ANSI-compliant. But if you don't mind proprietary T-SQL solutions, you can use the PARSENAME() function, which Microsoft designed to return a requested part of an object name. Because IP addresses are very similar to object names—four parts separated by dots—the PARSENAME() function fits the octet extraction from an IP address like a glove.
Here's an example of how to use the function to create a solution for this problem:
SELECT ip
FROM IPs
ORDER BY
CAST(PARSENAME(ip, 4)
AS tinyint),
CAST(PARSENAME(ip, 3)
AS tinyint),
CAST(PARSENAME(ip, 2)
AS tinyint),
CAST(PARSENAME(ip, 1)
AS tinyint);
This query invokes the PARSENAME() function in the ORDER BY clause once for each octet and converts the resulting octet string to tinyint to get the correct result.
All the solutions work similarly because they each perform a scan of the base table, then sort by four expressions. The main difference between the solutions is their complexity. This solution is very simple, but it's a proprietary solution because it uses the nonstandard PARSENAME() function. Also, it's not generic like the other solutions, which you can adapt for other scenarios that follow patterns, because the PARSNAME() function works only with exactly four elements.
It's always good when a solution to a T-SQL problem is both intuitive and simple. When the intuitive solution is complex, you should keep looking for other solutions. Apply logic and try different solutions until you come up with one you're satisfied with. And of course, keep practicing both T-SQL and pure logic.