Re: Convert IP address to decimal
Date: Mon, 8 Jun 2009 03:47:34 -0700 (PDT)
On Jun 8, 6:07†am, "Ń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.
Let's try an experiment:
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)) IP4FROM
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
DUAL; P1 P2 P3 P4
========== ========== ========== ========== 16777216 65536 256 1
Combining the two results:
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) IPFROM
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.
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Mon Jun 08 2009 - 05:47:34 CDT