Thanks for the question, Yoav.

Asked: November 09, 2009 - 1:22 pm UTC

Last updated: November 10, 2009 - 10:10 am UTC

Version: 10204

Viewed 10K+ times! This question is

You Asked

Hi Tom,
There is a function in MYSQL that allow to convert IP ADDRESS to Number.
The function name is: INET_ATON
http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_inet-aton

Is there an oracle built-in function for such a case (oracle version 10204) ?

Thanks
Yoav

and Tom said...


ops$tkyte%ORA11GR2> select ip_addr,
  2         to_number(substr(ip_addr,1,instr(ip_addr,'.')-1))*power(256,3)+
  3         to_number(substr(ip_addr,instr(ip_addr,'.')+1,instr(ip_addr,'.',1,2)-instr(ip_addr,'.')-1))*power(256,2)+
  4         to_number(substr(ip_addr,instr(ip_addr,'.',1,2)+1,instr(ip_addr,'.',1,3)-instr(ip_addr,'.',1,2)-1))*256+
  5         to_number(substr(ip_addr,instr(ip_addr,'.',1,3)+1)) inet_aton
  6  from
  7  (
  8  select '209.207.224.40' ip_addr from dual
  9  )
 10  /

IP_ADDR         INET_ATON
-------------- ----------
209.207.224.40 3520061480

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace function inet_aton( p_str in varchar2 ) return number
  2  as
  3      l_dot1 number := instr( p_str, '.',1,1 );
  4      l_dot2 number := instr( p_str, '.',1,2 );
  5      l_dot3 number := instr( p_str, '.',1,3 );
  6  begin
  7      return
  8         to_number(substr(p_str,1,l_dot1-1))*power(256,3)+
  9         to_number(substr(p_str,l_dot1+1, l_dot2-l_dot1-1))*power(256,2)+
 10         to_number(substr(p_str,l_dot2+1,l_dot3-l_dot2-1))*256+
 11         to_number(substr(p_str,l_dot3+1));
 12  end;
 13  /

Function created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select ip_addr, inet_aton( ip_addr )
  2  from
  3  (
  4  select '209.207.224.40' ip_addr from dual
  5  )
  6  /

IP_ADDR        INET_ATON(IP_ADDR)
-------------- ------------------
209.207.224.40         3520061480

and simply because I'm a pessimist...

ops$tkyte%ORA11GR2> select ip_addr, inet_aton( ip_addr ),
  2         mod( trunc(inet_aton( ip_addr )/256/256/256), 256 ) || '.' ||
  3         mod( trunc(inet_aton( ip_addr )/256/256), 256 ) || '.' ||
  4         mod( trunc(inet_aton( ip_addr )/256), 256 ) || '.' ||
  5         mod( inet_aton( ip_addr ), 256 )
  6  from
  7  (
  8  select '209.207.224.40' ip_addr from dual
  9  )
 10  /

IP_ADDR        INET_ATON(IP_ADDR)
-------------- ------------------
MOD(TRUNC(INET_ATON(IP_ADDR)/256/256/256),256)||'.'||MOD(TRUNC(INET_ATON(IP_ADDR)/256/256),256)||'.'||MOD(TRUNC(INET_ATON
-------------------------------------------------------------------------------------------------------------------------
209.207.224.40         3520061480
209.207.224.40

I have a feeling the reverse question might be lurking next.

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

or

Sokrates, November 11, 2009 - 5:58 am UTC

SQL> create or replace function inet_aton( p_str in varchar2 ) return number
  2  as
  3     ret number;
  4  begin
  5     select xmlquery(('(((' || replace(p_str, '.', ')*256+'))  returning content).getnumberval()
  6     into ret
  7     from dual;
  8  return ret;
  9  end inet_aton;
 10  /

Function created.

SQL> select inet_aton('209.207.224.40') from dual;   

INET_ATON('209.207.224.40')
---------------------------
                 3520061480

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

or

More to Explore

PL/SQL demos

PL/SQL docs

Equivalent MySQL function INET_ATON in oracle

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

or

More to Explore

PL/SQL demos

PL/SQL docs