在 SQL Server 中查找 IP 地址

Question

我在SQL Server中有超过1000万条记录包含IP地址。我需要将地址解析为单独的列以进一步分析它们。我尝试使用PATINDEX，它可以工作，但只涵盖IP地址的一种模式。第一个八位字节必须是 50、41 或 107。其他三个八位字节的范围可以从一位数到三位数字。这是我使用的 catpures 50/41/107.xxx.xxx.xxx：

SELECT  SUBSTRING(Column_1,PATINDEX('%[50|41|107].[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9]%',Column_1)-2,14)
FROM    table_1

如何在一个查询中捕获所有 IP 模式（50.x.xxx.xxx、50.xx.x.xxx 等）？

Answer 1

您可以使用

PARSENAME：

SELECT *
FROM dbo.YourTable
WHERE PARSENAME(YourColumn,4) = '50'

Answer 2

使用 Left 和 Charindex 字符串函数。试试这个。

select * from table_1 where
left(Column_1,charindex('.',Column_1)-1) in('50','41','107')

Answer 3

此查询将确保仅捕获只有 4 个八位字节的 IP 地址。

所有八位字节都需要在指定的范围内（当前设置为 0-255，但可以在 where 子句中更改）您还可以将 BETWEEN a 和 b 更改为 IN （a， b， ...），它只会捕获这些特定的数字。

它还将自己列中的八位字节作为整数返回，这将是存储它们的好方法，以防您需要对特定范围进行分析，然后您将来不需要解析文本。

注意：由于使用了TRY_CAST这仅适用于SQL Server 2012和

WITH Octet_Position (column_1, position_1, position_2, position_3)
AS
(
    SELECT
        column_1,
        CHARINDEX('.', column_1),
        CHARINDEX('.', column_1, CHARINDEX('.', column_1) + 1),
        CHARINDEX('.', column_1, (CHARINDEX('.', column_1, CHARINDEX('.', column_1) + 1)) + 1)
    FROM Table_1
)
SELECT
    column_1 AS ip_address,
    TRY_CAST(SUBSTRING(column_1, 0, position_1) AS INT) AS octet_1,
    TRY_CAST(SUBSTRING(column_1, position_1 + 1, position_2 - position_1 - 1) AS INT) AS octet_2,
    TRY_CAST(SUBSTRING(column_1, position_2 + 1, position_3 - position_2 -  1) AS INT) AS octet_3,
    TRY_CAST(SUBSTRING(column_1, position_3 + 1, LEN(column_1) - position_3) AS INT) AS octet_4
FROM Octet_Position
WHERE
    --make sure there are three .'s
    position_1 > 0 AND
    position_2 > 0 AND
    position_3 > 0 AND
    --Make sure the octets are numbers
    TRY_CAST(NULLIF(SUBSTRING(column_1, 0, position_1), '') AS INT) IS NOT NULL AND
    TRY_CAST(NULLIF(SUBSTRING(column_1, position_1 + 1, position_2 - position_1 - 1), '') AS INT) IS NOT NULL AND
    TRY_CAST(NULLIF(SUBSTRING(column_1, position_2 + 1, position_3 - position_2 -  1), '') AS INT) IS NOT NULL AND
    TRY_CAST(NULLIF(SUBSTRING(column_1, position_3 + 1, LEN(column_1) - position_3), '') AS INT) IS NOT NULL AND
    --make sure the octects are in the correct range
    TRY_CAST(NULLIF(SUBSTRING(column_1, 0, position_1), '') AS INT) BETWEEN 0 AND 255 AND
    TRY_CAST(NULLIF(SUBSTRING(column_1, position_1 + 1, position_2 - position_1 - 1), '') AS INT) BETWEEN 0 AND 255 AND
    TRY_CAST(NULLIF(SUBSTRING(column_1, position_2 + 1, position_3 - position_2 -  1), '') AS INT) BETWEEN 0 AND 255 AND
    TRY_CAST(NULLIF(SUBSTRING(column_1, position_3 + 1, LEN(column_1) - position_3), '') AS INT) BETWEEN 0 AND 255

Answer 4

--declare @ip nvarchar(15) = '1.2.3.4'
--declare @ip nvarchar(15) = '333.2.3.4'
declare @ip nvarchar(15) = '50.2.3.4'
select @ip, 
       iif (   ( patindex('[0-9]%.[0-9]%.[0-9]%.[0-9]%', @ip) = 0)
            or ( patindex('%[^0-9.]%', @ip) > 0 )
            or ( len(@ip) - len(replace(@ip, '.', '')) <> 3 )
            or ( parsename(@ip, 4) not between 0 and 255 )
            or ( parsename(@ip, 3) not between 0 and 255 )
            or ( parsename(@ip, 2) not between 0 and 255 )
            or ( parsename(@ip, 1) <> 50 )
           , 'bad'
           , 'ok'
         )