SQL 解析IP地址|极客教程

SQL 解析IP地址，你想把一个 IP 地址的各个字段分解为四列，考虑下面的 IP 地址。

SQL 解析IP地址问题描述

你想把一个 IP 地址的各个字段分解为四列，考虑下面的 IP 地址。

111.22.3.4

你希望查询语句能返回如下所示的结果。

A     B     C     D
----- ----- ----- ---
111   22    3     4

SQL 解析IP地址解决方案

具体的解决方案取决于数据库提供的内置函数。不管是哪种数据库，关键之处都在于如何快速定位英文句号以及英文句号前后的数字。
DB2
使用 WITH 子句递归地查询针对 IP 地址的循环操作，同时使用 SUBSTR 函数可以很容易解析 IP 地址。在 IP 地址开头加上一个英文句号，这样每一组数字的开头位置都有英文句号，因而我们能以相同的方式处理所有的四组数字。

 1 with x (pos,ip) as (
 2   values (1,'.92.111.0.222')
 3   union all
 4  select pos+1,ip from x where pos+1 <= 20
 5 )
 6  select max(case when rn=1 then e end) a,
 7         max(case when rn=2 then e end) b,
 8         max(case when rn=3 then e end) c,
 9         max(case when rn=4 then e end) d
10    from (
11  select pos,c,d,
12         case when posstr(d,'.') > 0 then substr(d,1,posstr(d,'.')-1)
13              else d
14         end as e,
15         row_number() over(order by pos desc) rn
16    from (
17  select pos, ip,right(ip,pos) as c, substr(right(ip,pos),2) as d
18    from x
19   where pos <= length(ip)
20     and substr(right(ip,pos),1,1) = '.'
21        ) x
22        ) y

MySQL
使用函数 SUBSTR_INDEX 很容易解析 IP 地址。

1 select substring_index(substring_index(y.ip,'.',1),'.',-1) a,
2        substring_index(substring_index(y.ip,'.',2),'.',-1) b,
3        substring_index(substring_index(y.ip,'.',3),'.',-1) c,
4        substring_index(substring_index(y.ip,'.',4),'.',-1) d
5   from (select '92.111.0.2' as ip from t1) y

Oracle
使用内置函数 SUBSTR 和 INSTR 解析和遍历 IP 地址。

1 select ip,
2        substr(ip, 1, instr(ip,'.')-1 ) a,
3        substr(ip, instr(ip,'.')+1,
4                    instr(ip,'.',1,2)-instr(ip,'.')-1 ) b,
5        substr(ip, instr(ip,'.',1,2)+1,
6                    instr(ip,'.',1,3)-instr(ip,'.',1,2)-1 ) c,
7        substr(ip, instr(ip,'.',1,3)+1 ) d
8   from (select '92.111.0.2' as ip from t1)

PostgreSQL
使用内置函数 SPLIT_PART 解析 IP 地址。

1 select split_part(y.ip,'.',1) as a,
2        split_part(y.ip,'.',2) as b,
3        split_part(y.ip,'.',3) as c,
4        split_part(y.ip,'.',4) as d
5   from (select cast('92.111.0.2' as text) as ip from t1) as y

SQL Server
使用 WITH 子句递归地查询针对 IP 地址的循环操作，同时使用 SUBSTR 函数可以很容易解析 IP 地址。在 IP 地址开头加上英文句号，这样每一组数字的开头位置都有英文句号，因而我们能以相同的方式处理全部 4 组数字。

 1   with x (pos,ip) as (
 2     select 1 as pos,'.92.111.0.222' as ip from t1
 3     union all
 4    select pos+1,ip from x where pos+1 <= 20
 5   )
 6   select max(case when rn=1 then e end) a,
 7          max(case when rn=2 then e end) b,
 8          max(case when rn=3 then e end) c,
 9          max(case when rn=4 then e end) d
10     from (
11   select pos,c,d,
12          case when charindex('.',d) > 0
13               then substring(d,1,charindex('.',d)-1)
14               else d
15          end as e,
16          row_number() over(order by pos desc) rn
17     from (
18   select pos, ip,right(ip,pos) as c,
19          substring(right(ip,pos),2,len(ip)) as d
20     from x
21   where pos <= len(ip)
22     and substring(right(ip,pos),1,1) = '.'
23        ) x
24        ) y