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
--

On Jun 8, 6:07*am, "Álvaro G. Vicario"
<alvaro.NOSPAMTH... (AT) demogracia (DOT) com> wrote:
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))*PO WER(2,24)
+ TO_NUMBER(REGEXP_SUBSTR('127.0.0.1','\w+',1,2))*PO WER(2,16)
+ TO_NUMBER(REGEXP_SUBSTR('127.0.0.1','\w+',1,3))*PO WER(2,8)
+ TO_NUMBER(REGEXP_SUBSTR('127.0.0.1','\w+',1,4))*PO WER(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.

Charles Hooper wrote:
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

Peter Nilsson escribió:
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.


--
-- 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
--