Oracle: Converting an IPv4 address to a number?

Question

How can I convert an IPv4 address in dot-decimal format to a number? For example, the address 39.255.2.51 should be converted to 4026466867.

Answer 1

• extract the four octets of the address using regexp_substr.
• reconstitute the value by shifting the octets back to their original positions.

The regexp might be relatively expensive, so if you're doing this a lot you might consider caching the numeric value in your table alongside the IP address.

with addr as (select '239.255.2.51' ip from dual)
select ip, to_number(regexp_substr(ip, '\d+', 1, 1)) * 16777216 +
           to_number(regexp_substr(ip, '\d+', 1, 2)) * 65536 +
           to_number(regexp_substr(ip, '\d+', 1, 3)) * 256 +
           to_number(regexp_substr(ip, '\d+', 1, 4)) n
  from addr;

IP                     N
------------- ----------      
239.255.2.51  4026466867 

For completeness, here's how to go the other way.

with addr as (select 4026466867 n from dual)
select n, mod(trunc(n/16777216),256) ||'.'||
          mod(trunc(n/65536),   256) ||'.'||
          mod(trunc(n/256),     256) ||'.'||
          mod(n,                256) ip
from addr;

         N IP                                                                                                                                                                                                                                                             
---------- ------------
4026466867 239.255.2.51