如何用SQLite的INSTR函数将IP地址拆分为4个整数

针对标准格式[0-255].[0-255].[0-255].[0-255]的IP地址，以下是修正后的拆分代码，附带逐段逻辑说明：

-- 提取第一段：从IP开头到第一个'.'之前的内容
CAST(SUBSTRING(ip_address, 1, INSTR(ip_address, '.') - 1) AS INT) AS ip1,

-- 提取第二段：第一个'.'之后，到第二个'.'之前的内容
CAST(
  SUBSTRING(
    ip_address,
    INSTR(ip_address, '.') + 1,
    INSTR(ip_address, '.', INSTR(ip_address, '.') + 1) - (INSTR(ip_address, '.') + 1)
  ) AS INT
) AS ip2,

-- 提取第三段：第二个'.'之后，到第三个'.'之前的内容
CAST(
  SUBSTRING(
    ip_address,
    INSTR(ip_address, '.', INSTR(ip_address, '.') + 1) + 1,
    INSTR(ip_address, '.', INSTR(ip_address, '.', INSTR(ip_address, '.') + 1) + 1) - (INSTR(ip_address, '.', INSTR(ip_address, '.') + 1) + 1)
  ) AS INT
) AS ip3,

-- 提取第四段：第三个'.'之后的所有内容
CAST(
  SUBSTRING(
    ip_address,
    INSTR(ip_address, '.', INSTR(ip_address, '.', INSTR(ip_address, '.') + 1) + 1) + 1
  ) AS INT
) AS ip4

关键函数说明 #

• INSTR(str, substr[, start])：从start位置开始（可选，默认从开头）查找substr在str中第一次出现的位置，返回对应索引（从1开始计数）。
• SUBSTRING(str, start[, length])：从start索引开始截取字符串，指定length则截取对应长度，不指定则截取到字符串结尾。

逐段逻辑拆解 #

1. ip1（第一段）
   用INSTR(ip_address, '.')找到第一个.的位置，减1得到第一段的最后一个字符索引，从开头截取到该位置后转成整数。

2. ip2（第二段）

   • 第一个.的位置+1是第二段的起始索引；
   • 用INSTR(ip_address, '.', 第一个.位置+1)找到第二个.的位置，用这个位置减去起始索引得到第二段的长度；
   • 截取对应区间的字符串转成整数。

3. ip3（第三段）

   • 基于第二个.的位置+1得到第三段的起始索引；
   • 再调用一次INSTR找到第三个.的位置，计算出第三段的长度；
   • 截取对应区间转成整数。

4. ip4（第四段）
   找到第三个.的位置后+1，直接从该位置截取到字符串结尾即可——因为第四段是IP的最后一部分，后面没有分隔符，不需要指定长度，转成整数后就是目标值。

你原有代码的问题 #

• ip1的写法会包含第一个.（比如192.），虽然SQLite转int时会自动忽略后续非数字字符，但规范写法应该用INSTR(...) -1截取到.之前。
• 原有ip3的逻辑错误，截取的内容包含了第四段的部分字符（比如1.1），转int后只会保留前面的数字1，不符合需求。
• 原有ip4的写法逻辑完全错误，SUBSTRING的负起始索引在SQLite中是从字符串末尾开始计数，这和你要取最后一段的需求不匹配。

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