Re: Convert IP address to decimal

From: Charles Hooper <>
Date: Mon, 8 Jun 2009 03:47:34 -0700 (PDT)
Message-ID: <>

On Jun 8, 6:07†am, "Ńlvaro G. Vicario"
<> wrote:
> Iím looking for an Oracle function to convert a dot-format IP address
> into its numeric equivalent ('' --> 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:
  '' IP

Now use regular expression's substring to break this up into "words": SELECT

  TO_NUMBER(REGEXP_SUBSTR('','\w+',1,1)) IP1,
  TO_NUMBER(REGEXP_SUBSTR('','\w+',1,2)) IP2,
  TO_NUMBER(REGEXP_SUBSTR('','\w+',1,3)) IP3,
  TO_NUMBER(REGEXP_SUBSTR('','\w+',1,4)) IP4
  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('','\w+',1,2))*POWER(2,16)
  + TO_NUMBER(REGEXP_SUBSTR('','\w+',1,3))*POWER(2,8)
  + TO_NUMBER(REGEXP_SUBSTR('','\w+',1,4))*POWER(2,0) IP
  DUAL;         IP


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. Received on Mon Jun 08 2009 - 05:47:34 CDT

Original text of this message