I often emphasize the importance of logic in T-SQL problem solving. In recent columns, I've given you a T-SQL puzzle and a purely logical puzzle to help you improve your logic skills. This article's T-SQL puzzle will stretch your T-SQL skills as you try to derive the best solution. In the sidebar "The Logical Puzzle," page 30, I present a new logic problem to try. Do your best to solve the puzzles yourself before looking at my solutions. If the trivial and intuitive approaches you come up with seem cumbersome, try to be creative and think outside the box.
Sorting IP Addresses
This month's puzzle involves sorting IP addresses. Your task is to write a query that sorts the IP addresses that are stored in the IPs table. Note that a correct IP sort logically means that you should consider each octet separately and as a number, as opposed to the way SQL Server stores it—as a character string. Run Listing 1's code to create the IPs table and populate it with sample data. Figure 1 shows the desired result. The first sort value is the first octet, so IP addresses with 3 in the first octet should be sorted first, then addresses with 22 in the first octet, then those with 192. The second sort value is the second octet—for example, among IP addresses that have 3 in the first octet, the one with 8 in the second octet should be sorted first, then those with 77 in the second octet. Similarly, the third octet is the third sort value, and the fourth octet is the fourth sort value.
If you attempt to simply sort the IP addresses by the ip column as follows
SELECT * FROM IPs ORDER BY ip;
you get the incorrect IP address sort that Figure 2 shows: the IP addresses sorted by their character-string representation. SQL Server compares characters in corresponding positions from left to right. Thus, IP addresses that start with the number 1 (e.g., 192.*) are listed before IP addresses starting with the number 2 (e.g., 22.*), which come before addresses starting with 3 (e.g., 3.*). Also, SQL Server doesn't recognize that an IP address is made up of four separate parts; it treats each IP address as a character string.
A different table design could have made sorting the IP addresses simple. However, for the sake of this exercise, assume that you have to face the task with the given table design.
You now have all the information you need to start working on the problem. I suggest you examine my solutions after trying to solve the problem yourself.
Trivial Solution: Using Lengthy Expressions
An intuitive approach to solving the problem is to specify four expressions in the ORDER BY clause, breaking the ip column into the four octets by using the SUBSTRING() and the CHARINDEX() functions, then converting them to integers. The following pseudo code shows how the solution query would look:
SELECT ip
FROM IPs
ORDER BY
CAST(SUBSTRING(ip, 1, p1 - 1 ) AS tinyint),
CAST(SUBSTRING(ip, p1 + 1, p2 - p1 - 1) AS tinyint),
CAST(SUBSTRING(ip, p2 + 1, p3 - p2 - 1) AS tinyint),
CAST(SUBSTRING(ip, p3 + 1, 3 ) AS tinyint);
P1, p2, and p3 respectively represent the positions of the first, second, and third dots; you need to replace them with expressions that actually calculate each dot's position.
You calculate the position of the first dot by using the following expression:
CHARINDEX('.', ip) AS p1
To calculate the position of the second dot, you use a similar expression, but add a third argument to CHARINDEX() telling the function where to start looking for the dot. This third argument is p1+1 (the position number of the first dot plus 1):
CHARINDEX('.', ip, CHARINDEX('.', ip) + 1) AS p2
Similarly, to calculate the position of the third dot, you need to provide p2+1 as the third argument to CHARINDEX(), resulting in the following expression:
CHARINDEX('.', ip, CHARINDEX('.', ip, CHARINDEX('.', ip)
+ 1) + 1) AS p3
Replacing p1, p2, and p3 in the pseudo code with the previous expressions gives you the solution query that Listing 2 shows.
Obviously, this solution query is long and hard to follow. If you later need to revise the query, you're bound to introduce bugs, so I don't recommend this solution.
Simplified Solution: Using Derived Tables
The first solution contained lengthy expressions, mainly because of the nesting required for the CHARINDEX() function's third argument. By using derived tables, you can significantly simplify the solution. Derived tables let you reuse the aliases you assign to expressions in the SELECT list, as Listing 3 shows.
In the innermost derived table (D1), you calculate the position of the first dot by using this expression:
CHARINDEX('.', ip) AS p1
In the next level's derived table (D2), you reuse the p1 alias to calculate the position of the second dot:
CHARINDEX('.', ip, p1+1) AS p2
Similarly, in the next level's derived table (D3), you reuse the p2 alias to calculate the position of the third dot:
CHARINDEX('.', ip, p2+1) AS p3
Now that you've calculated all dot positions and given them aliases, the outermost query against the derived table D3 simply uses those aliases in the ORDER BY clause's SUBSTRING() functions.
Prev. page
[1]
2
next page 
