如何从SQL字符串变量中提取指定格式的IP地址？

现有以下SQL字符串变量：

DECLARE @IPData_1 nvarchar(max) = '{"ODBC":"Connection-StringIPX-IP=10.60.96.230&X-Local}'
DECLARE @IPData_2 nvarchar(max) = '{"ODBC":"Connection-StringIPX-IP:1.60.0.230X-Local=1}'
DECLARE @IPData_3 nvarchar(max) = '{"ODBC":"Connection-StringIPX-For=233.60.255.120TransactionID:2}'
DECLARE @IPData_4 nvarchar(max) = '{"ODBC":"Connection-StringIPX-For:1.1.1.90ID=1}'

需要从中提取出对应的IP地址：

10.60.96.230
1.60.0.230
233.60.255.120
1.1.1.90

尝试了以下SQL语句但未得到正确结果：

SELECT
    SUBSTRING(@IPData_1,CHARINDEX('IPX-IP=',@IPData_1)+7,CHARINDEX('.',@IPData_1)),
    SUBSTRING(@IPData_1,CHARINDEX('IPX-IP:',@IPData_1)+7,CHARINDEX('.',@IPData_1)),
    SUBSTRING(@IPData_1,CHARINDEX('IPX-For=',@IPData_1)+7,CHARINDEX('.',@IPData_1)),
    SUBSTRING(@IPData_1,CHARINDEX('IPX-For:',@IPData_1)+7,CHARINDEX('.',@IPData_1))

原语句问题分析 #

• SUBSTRING第三个参数使用CHARINDEX('.',@IPData_1)仅能定位到第一个.的位置，长度计算错误，导致截取的IP不完整。
• 同时对单个变量执行多个不同前缀的查找逻辑，逻辑混乱，无法匹配对应变量的正确前缀。

单个变量处理方案 #

针对每个变量的特定前缀，结合CHARINDEX定位IP起始位置，使用PATINDEX匹配IP后的第一个非IP字符，从而确定IP的截取长度：

-- 提取@IPData_1的IP
SELECT SUBSTRING(@IPData_1, 
                 CHARINDEX('IPX-IP=', @IPData_1) + 7, 
                 PATINDEX('%[^0-9.]%', SUBSTRING(@IPData_1, CHARINDEX('IPX-IP=', @IPData_1) + 7, LEN(@IPData_1))) - 1) AS ExtractedIP;

-- 提取@IPData_2的IP
SELECT SUBSTRING(@IPData_2, 
                 CHARINDEX('IPX-IP:', @IPData_2) + 7, 
                 PATINDEX('%[^0-9.]%', SUBSTRING(@IPData_2, CHARINDEX('IPX-IP:', @IPData_2) + 7, LEN(@IPData_2))) - 1) AS ExtractedIP;

-- 提取@IPData_3的IP
SELECT SUBSTRING(@IPData_3, 
                 CHARINDEX('IPX-For=', @IPData_3) + 8, 
                 PATINDEX('%[^0-9.]%', SUBSTRING(@IPData_3, CHARINDEX('IPX-For=', @IPData_3) + 8, LEN(@IPData_3))) - 1) AS ExtractedIP;

-- 提取@IPData_4的IP
SELECT SUBSTRING(@IPData_4, 
                 CHARINDEX('IPX-For:', @IPData_4) + 8, 
                 PATINDEX('%[^0-9.]%', SUBSTRING(@IPData_4, CHARINDEX('IPX-For:', @IPData_4) + 8, LEN(@IPData_4))) - 1) AS ExtractedIP;

批量处理方案 #

将所有变量存入临时表，通过CASE分支匹配不同前缀，统一提取IP：

DECLARE @IPTable TABLE (IPData nvarchar(max));
INSERT INTO @IPTable VALUES
(@IPData_1),
(@IPData_2),
(@IPData_3),
(@IPData_4);

SELECT 
    SUBSTRING(IPData, 
              CASE 
                  WHEN CHARINDEX('IPX-IP=', IPData) > 0 THEN CHARINDEX('IPX-IP=', IPData) + 7
                  WHEN CHARINDEX('IPX-IP:', IPData) > 0 THEN CHARINDEX('IPX-IP:', IPData) + 7
                  WHEN CHARINDEX('IPX-For=', IPData) > 0 THEN CHARINDEX('IPX-For=', IPData) + 8
                  WHEN CHARINDEX('IPX-For:', IPData) > 0 THEN CHARINDEX('IPX-For:', IPData) + 8
              END,
              PATINDEX('%[^0-9.]%', SUBSTRING(IPData, 
                                             CASE 
                                                 WHEN CHARINDEX('IPX-IP=', IPData) > 0 THEN CHARINDEX('IPX-IP=', IPData) + 7
                                                 WHEN CHARINDEX('IPX-IP:', IPData) > 0 THEN CHARINDEX('IPX-IP:', IPData) + 7
                                                 WHEN CHARINDEX('IPX-For=', IPData) > 0 THEN CHARINDEX('IPX-For=', IPData) + 8
                                                 WHEN CHARINDEX('IPX-For:', IPData) > 0 THEN CHARINDEX('IPX-For:', IPData) + 8
                                             END, LEN(IPData))) - 1) AS ExtractedIP
FROM @IPTable;

关键逻辑说明 #

• PATINDEX('%[^0-9.]%', ...)：匹配IP地址后第一个非数字、非.的字符，以此确定IP的结束位置。
• 前缀长度计算：IPX-IP=/IPX-IP:长度为7，所以起始位置要+7；IPX-For=/IPX-For:长度为8，起始位置要+8。

内容的提问来源于stack exchange，提问作者MAK