Recipe6.15.Parsing an IP Address

Recipe 6.15. Parsing an IP Address

Problem

You want to parse an IP address's fields into columns. Consider the following IP address:

 111.22.3.4 

You would like the result of your query to be:

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

Solution

The solution depends on the built-in functions provided by your DBMS. Regardless of your DBMS, being able to locate periods and the numbers immediately surrounding them are the keys to the solution.

DB2

Use the recursive WITH clause to simulate an iteration through the IP address while using SUBSTR to easily parse it. A leading period is added to the IP address so that every set of numbers has a period in front of it and can be treated the same way.

  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

The function SUBSTR_INDEX makes parsing an IP address an easy operation:

 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

Use the built-in function SUBSTR and INSTR to parse and navigate through the IP address:

 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

Use the built-in function SPLIT_PART to parse an IP address:

 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

Use the recursive WITH clause to simulate an iteration through the IP address while using SUBSTR to easily parse it. A leading period is added to the IP address so that every set of numbers has a period in front of it and can be treated the same way:

  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 

Discussion

By using the built-in functions for your database, you can easily walk through parts of a string. The key is being able to locate each of the periods in the address. Then you can parse the numbers between each.