CODEWITHSUNDEEP
t-sqlsorting
t-clausen.dk
t-clausen.dk

Reputation: 44326

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 ?

Upvotes: 3

Views: 2363

Answers (3)

Jasper Bekkers
Jasper Bekkers

Reputation: 6809

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.

Upvotes: 1

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32700

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.

Upvotes: 8

Paul Dixon
Paul Dixon

Reputation: 300935

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.

Upvotes: 1

Related Questions