Re: Convert IP address to decimal
From: Álvaro G. Vicario <alvaro.NOSPAMTHANX_at_demogracia.com>
Date: Mon, 15 Jun 2009 13:14:10 +0200
Message-ID: <h15ae3$528$1_at_news.eternal-september.org>
Peter Nilsson escribió:
> Charles Hooper wrote:
> <snip>
>
> 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).
Date: Mon, 15 Jun 2009 13:14:10 +0200
Message-ID: <h15ae3$528$1_at_news.eternal-september.org>
Peter Nilsson escribió:
> 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).
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^0END 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 --Received on Mon Jun 15 2009 - 06:14:10 CDT