Re: Convert IP address to decimal

From: Peter Nilsson <airia_at_acay.com.au>
Date: Mon, 8 Jun 2009 22:51:40 -0700 (PDT)
Message-ID: <fb905629-61e7-456d-999f-04e7159521ca_at_a36g2000yqc.googlegroups.com>

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

Received on Tue Jun 09 2009 - 00:51:40 CDT