在TSQL中对IP地址进行排序 [英] Sorting IP addresses in TSQL

问题描述

我需要对我的IP列表进行排序.问题是我希望它们按照第一部分中的值排序，然后是第二部分中的值，依此类推.

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

此代码未正确订购IP

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

有人可以帮助我吗?

推荐答案

尽管它不是为IP地址设计的，但您可以使用PARSENAME通过按句点分割将字符串分成多个部分.

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

尽管我看到您的IP地址带有冒号而不是句点，所以您只需要将所有冒号替换为句点即可.

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.

因此，您可以这样做:

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)

有关详细信息，请参见 MSDN链接.

See the MSDN link for more information.

这篇关于在TSQL中对IP地址进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！