Convert IP address to decimal

["Álvaro G. Vicario"]

I�m looking for an Oracle function to convert a dot-format IP address
into its numeric equivalent ('127.0.0.1' --> 2130706433). I�ve googled
for "ip2long", "inet_ntoa", "IP to decimal"... but I only get links to
the PHP, MySQL or C implementations.

I don�t mind writing my own function but my PL/SQL skills are minimum
and I can�t go past the initial step of splitting the string into four
numbers (I can't find an Oracle equivalent of the split/explode
functions available in other languages).

How can I proceed?

I�m running Oracle 10g XE on Windows XP. Thank you in advance.

--
-- http://alvaro.es - �lvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programaci�n web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

[Charles Hooper]

On Jun 8, 6:07 am, "Álvaro G. Vicario"
<alvaro.NOSPAMTH...@demogracia.com> wrote:
> I’m looking for an Oracle function to convert a dot-format IP address
> into its numeric equivalent ('127.0.0.1' --> 2130706433). I’ve googled

> for "ip2long", "inet_ntoa", "IP to decimal"... but I only get links to
> the PHP, MySQL or C implementations.
>

> I don’t mind writing my own function but my PL/SQL skills are minimum
> and I can’t go past the initial step of splitting the string into four

> numbers (I can't find an Oracle equivalent of the split/explode
> functions available in other languages).
>
> How can I proceed?
>

> I’m running Oracle 10g XE on Windows XP. Thank you in advance.

Let's try an experiment:
SELECT
'127.0.0.1' IP
FROM
DUAL;

IP
=========
127.0.0.1

Now use regular expression's substring to break this up into "words":
SELECT
TO_NUMBER(REGEXP_SUBSTR('127.0.0.1','\w+',1,1)) IP1,
TO_NUMBER(REGEXP_SUBSTR('127.0.0.1','\w+',1,2)) IP2,
TO_NUMBER(REGEXP_SUBSTR('127.0.0.1','\w+',1,3)) IP3,
TO_NUMBER(REGEXP_SUBSTR('127.0.0.1','\w+',1,4)) IP4
FROM
DUAL;

IP1 IP2 IP3 IP4
========== ========== ========== ==========
127 0 0 1

Now an experiment with the POWER function:
SELECT
POWER(2,24) P1,
POWER(2,16) P2,
POWER(2,8) P3,
POWER(2,0) P4
FROM
DUAL;

P1 P2 P3 P4
========== ========== ========== ==========
16777216 65536 256 1

Combining the two results:
SELECT
TO_NUMBER(REGEXP_SUBSTR('127.0.0.1','\w+',1,1))*POWER(2,24)
+ TO_NUMBER(REGEXP_SUBSTR('127.0.0.1','\w+',1,2))*POWER(2,16)
+ TO_NUMBER(REGEXP_SUBSTR('127.0.0.1','\w+',1,3))*POWER(2,8)
+ TO_NUMBER(REGEXP_SUBSTR('127.0.0.1','\w+',1,4))*POWER(2,0) IP
FROM
DUAL;

IP
==========
2130706433

There are probably a couple other solutions also - in this case there
is no need for PL/SQL. Note that the "*POWER(2,0)" could be omitted -
it was included for the sake of completeness.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

[Peter Nilsson]

Charles Hooper wrote:
> <alvaro.NOSPAMTH...@demogracia.com> wrote:
> > I’m looking for an Oracle function to convert a dot-format IP address

> > into its numeric equivalent ('127.0.0.1' --> 2130706433). ...

> > I’m running Oracle 10g XE on Windows XP. Thank you in advance.

<snip>

> SELECT
> TO_NUMBER(REGEXP_SUBSTR('127.0.0.1','\w+',1,1))*POWER(2,24)
> + TO_NUMBER(REGEXP_SUBSTR('127.0.0.1','\w+',1,2))*POWER(2,16)
> + TO_NUMBER(REGEXP_SUBSTR('127.0.0.1','\w+',1,3))*POWER(2,8)
> + TO_NUMBER(REGEXP_SUBSTR('127.0.0.1','\w+',1,4))*POWER(2,0) IP
> FROM
> DUAL;

I'd use \d rather than \w as it is more robust with regards to
'invalid' input...

with
addresses as
(
select '127.0.0.1' ip from dual union all
select 'localhost' 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))
from addresses;

Note that both are obviously dot-decimal dependant. Older IP
systems allowed C style octal (leading 0) or hex (leading 0x).

--
Peter

["Álvaro G. Vicario"]

Peter Nilsson escribi�:

> Charles Hooper wrote:
>> <alvaro.NOSPAMTH...@demogracia.com> wrote:

>>> I�m looking for an Oracle function to convert a dot-format IP address

>>> into its numeric equivalent ('127.0.0.1' --> 2130706433). ...

>>> I�m running Oracle 10g XE on Windows XP. Thank you in advance.

> <snip>
>> SELECT
>> TO_NUMBER(REGEXP_SUBSTR('127.0.0.1','\w+',1,1))*POWER(2,24)
>> + TO_NUMBER(REGEXP_SUBSTR('127.0.0.1','\w+',1,2))*POWER(2,16)
>> + TO_NUMBER(REGEXP_SUBSTR('127.0.0.1','\w+',1,3))*POWER(2,8)
>> + TO_NUMBER(REGEXP_SUBSTR('127.0.0.1','\w+',1,4))*POWER(2,0) IP
>> FROM
>> DUAL;
>
> I'd use \d rather than \w as it is more robust with regards to
> 'invalid' input...
>
> with
> addresses as
> (
> select '127.0.0.1' ip from dual union all
> select 'localhost' 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))
> from addresses;
>
> Note that both are obviously dot-decimal dependant. Older IP
> systems allowed C style octal (leading 0) or hex (leading 0x).

I've composed your suggestions into a function that looks like this:

CREATE OR REPLACE FUNCTION IP2LONG (
IP_STRING IN VARCHAR2
) RETURN NUMBER DETERMINISTIC
IS
BEGIN
RETURN
TO_NUMBER(REGEXP_SUBSTR(IP_STRING, '\d+', 1, 1)) * 16777216 + -- 2^24
TO_NUMBER(REGEXP_SUBSTR(IP_STRING, '\d+', 1, 2)) * 65536 + -- 2^16
TO_NUMBER(REGEXP_SUBSTR(IP_STRING, '\d+', 1, 3)) * 256 + -- 2^8
TO_NUMBER(REGEXP_SUBSTR(IP_STRING, '\d+', 1, 4)); -- 2^0
END IP2LONG;

It could use a better name and some parameter checking but it'll do the
job for now. Thanks to both.