February 15, 2005 03:15 PM

Sorting IP Addresses

Take the next logical step—out of the box
Rating: (0)
SQL Server Magazine
InstantDoc ID #44942
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.

...This article is for paid Professional Members only.

Already a Professional Member? Please log in now:

NOT A PROFESSIONAL MEMBER? YOU CHOOSE:

Professional Membership

Monthly

Annual

VIP Membership

Monthly

Annual

Add a Comment

>>as opposed to the way SQL Server stores it—as a character string.

I had no idea that SQL Server stored IP addresses in any particular way. Who is to say how SQL Server stores an IP address? The person who creats the table. Yes, it is possible that the choice will be to store it as a string. It is also possible that the choice will be to store it as four tinyint columns. Of course you wouldn't have anything to write about in that case.


Your usual good stuff, Itzak. Just giving you a hard time. 8-)

royharv2 2/15/2006 1:37:39 PM


We can sort first by positions of dots and for ip addresses with same dot positions by length if and ip:
SELECT * FROM IPs
ORDER BY
CHARINDEX('.', ip),
CHARINDEX('.', ip, CHARINDEX('.', ip) + 1),
LEN(ip) - CHARINDEX('.', REVERSE(ip)),
LEN(ip),
ip

Also we can write a UDF (fn_replace_numbers for instance) that replaces all numbers in a given string with a given character (in our case this char must be biggier than '.', '_' for instane). Then we can use:
SELECT *
FROM IPs
ORDER BY dbo.fn_replace_numbers(ip, '_'), ip

dimdim 2/21/2005 6:53:40 AM


if you use the parsename function, it is much easier.

that won't work for IPv6

but select parsename('192.168.5.1', 4) will return the 1st node

select parsename('192.168.5.1', 3) will return the 2nd node

select parsename('192.168.5.1', 2) will return the 3rd node

select parsename('192.168.5.1', 1) will return the last node

you'll need to convert them to an int.. i usually just write a UDF to do this.. so i dont have duplicated logic everywhere

email/msn messenger: aaron_kempf@hotmail.com


Anonymous User 2/17/2005 1:36:16 PM


You must log on before posting a comment.

Are you a new visitor? Register Here