Sorting IP addresses in TSQL

问题:

I need my IP list sorted. Problem is that i want them sorted after the value in the first part, then second part and so on.

MYTABLE

DATA
20.1.2.1
100.1.1.1
20.1.10.1
80.8.8.8

This code doesn't order the IP correct

SELECT * FROM MYTABLE ORDER BY DATA

I was hoping to get something like this:

20.1.2.1
20.1.10.1
80.8.8.8
100.1.1.1

Can anyone help me ?

回答1:

Although it wasn't designed for IP addresses, you can use PARSENAME to divide a string into sections by splitting on the period.

I see though that you have IP addresses with a colon instead of a period, so you would just need to replace all your colons with a period.

Thus, you could do:

SELECT *
FROM MyTable
ORDER BY CAST(PARSENAME(REPLACE(Data, ':', '.'), 4) as int),
    CAST(PARSENAME(REPLACE(Data, ':', '.'), 3) as int),
    CAST(PARSENAME(REPLACE(Data, ':', '.'), 2) as int),
    CAST(PARSENAME(REPLACE(Data, ':', '.'), 1) as int)

You can throw this in Query Analyzer to confirm it works:

SELECT *
FROM (
    SELECT '20:1:2:1' AS Data UNION
    SELECT '100:1:1:1' UNION
    SELECT '20:1:10:1' UNION
    SELECT '80:8:8:8'
) X
ORDER BY CAST(PARSENAME(REPLACE(Data, ':', '.'), 4) as int),
    CAST(PARSENAME(REPLACE(Data, ':', '.'), 3) as int),
    CAST(PARSENAME(REPLACE(Data, ':', '.'), 2) as int),
    CAST(PARSENAME(REPLACE(Data, ':', '.'), 1) as int)

See the MSDN link for more information.

回答2:

This is something you'd use the INET_ATON() function in MySQL, while that doesn't exist for T-SQL, see this question for examples of how it can be done.

回答3:

Store the IP addresses as plain 32-bit integers (it's guaranteed they fit) and sort those. For IPv6 convert to a 128-bit integer instead.