Re: Convert IP address to decimal

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Mon, 8 Jun 2009 03:47:34 -0700 (PDT)
Message-ID: <a95428fc-4e45-4a9e-b42b-66091ffc0868_at_l32g2000vba.googlegroups.com>

On Jun 8, 6:07�am, "�lvaro G. Vicario"
<alvaro.NOSPAMTH..._at_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. Received on Mon Jun 08 2009 - 05:47:34 CDT