CODEWITHSUNDEEP
oracle-databaseip
Mark Harrison
Mark Harrison

Reputation: 304654

Oracle: Converting an IPv4 address to a number?

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.

Upvotes: 3

Views: 4299

Answers (2)

evan_b
evan_b

Reputation: 1249

My need is primarily int > IP, with occasional negative ints. For negative values, Mark's answer doesn't work; each octet is returned as a negative number.

This is what I came up with that works for both positive and negative integers, at least for as many examples as I looked at. (This should work on most DBs except SQL server - you'll need to use the '%' function there instead.)

select
mod(mod(floor(ip/16777216),256) + 256, 256) ||'.'||
mod(mod(floor(ip/65536),   256) + 256, 256) ||'.'||
mod(mod(floor(ip/256),     256) + 256, 256) ||'.'||
mod(mod(ip,                256) + 256, 256) as decoded_ip
from addr;

The method for calculating mod on negative numbers is taken from here: Mod negative numbers in SQL just like excel

Upvotes: 0

Mark Harrison
Mark Harrison

Reputation: 304654

  • 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

Upvotes: 6

Related Questions