分解IP地址的SQL写法

Mysql中很简单使用instrsubstring_index()

Oracle也比较简单，使用regexp_index()来搞定，DB2中貌似没有此类函数，手工使用with 递归与substr来实现，给出两种写法：

CREATE TABLE DB2INST1.IP_TAB
 (
 IP VARCHAR (20)
 );

INSERT INTO DB2INST1.IP_TAB (IP)
VALUES ('192.168.10.110');

INSERT INTO DB2INST1.IP_TAB (IP)
VALUES ('192.168.10.111');
方法1，用with模拟对IP的反复操作，在最前面加一个.

WITH x (POS, ip) AS
    (SELECT 1, '.'||ip
     FROM IP_TAB UNION ALL
    SELECT pos + 1, IP
    FROM x
    WHERE POS + 1 <= 20)
SELECT substr(ip,2,length(ip)-1) ip
    , max (CASE WHEN rn = 1 THEN e END) a
    , max (CASE WHEN rn = 2 THEN e END) b
    , max (CASE WHEN rn = 3 THEN e END) c
    , max (CASE WHEN rn = 4 THEN e END) d
FROM (SELECT ip, pos, c, d
          , CASE WHEN posstr (d, '.') > 0 THEN substr (d, 1, posstr (d, '.') -1) ELSE d END e
          , row_number () over (PARTITION BY ip ORDER BY pos DESC) rn
      FROM (SELECT pos, ip, RIGHT (ip, pos) AS c, substr (RIGHT (ip, pos), 2) AS d FROM x WHERE pos <= length (IP) AND substr (RIGHT (ip, POS), 1, 1) = '.'))
GROUP BY IP

方法2，是群中网友流水无声写的，我改造了一下，此方法效率更高一些

WITH x (iorder,IP, ori, pos) AS
    (
    SELECT 1,ip||'.',1,locate('.',ip)
    FROM IP_TAB
    UNION ALL
    SELECT iorder+1, IP, pos + length ('.'), locate ('.', IP, pos + length ('.'))
    FROM x
    WHERE locate ('.', IP, pos + length ('.')) > 0)
SELECT IP,max(CASE WHEN iorder=1 THEN SUBSTR (IP, ori, pos - ori) end) a,
           max(CASE WHEN iorder=2 THEN SUBSTR (IP, ori, pos - ori) end) b,
           max(CASE WHEN iorder=3 THEN SUBSTR (IP, ori, pos - ori) end) c,
           max(CASE WHEN iorder=4 THEN SUBSTR (IP, ori, pos - ori) end) d -- iorder,IP,ori,pos,SUBSTR (IP, ori, pos - ori) AS RESULT
FROM x
GROUP BY  IP

执行结果如下: