Home » SQL & PL/SQL » SQL & PL/SQL » Fetch IP Addresses between given IP Range (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit)
| Fetch IP Addresses between given IP Range [message #623693] |
Fri, 12 September 2014 12:01  |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Hi,
I was just writing a query for fetching IP Address from a table, which is in between given IP Range.
I have written below query, which seems to work fine to me.
WITH temp
AS (SELECT '10.250.2.0' from_ip,
'10.251.1.254' to_ip
FROM DUAL)
SELECT R.IP_DISPLAY_V4
FROM table_with_ip_address r, temp t
WHERE TO_NUMBER ( REGEXP_SUBSTR (r.IP_DISPLAY_V4,
'\w+',
1,
1)
|| LPAD (TO_NUMBER (REGEXP_SUBSTR (r.IP_DISPLAY_V4,
'\w+',
1,
2)), 3, 0)
|| LPAD (TO_NUMBER (REGEXP_SUBSTR (r.IP_DISPLAY_V4,
'\w+',
1,
3)), 3, 0)
|| LPAD (TO_NUMBER (REGEXP_SUBSTR (r.IP_DISPLAY_V4,
'\w+',
1,
4)), 3, 0)) BETWEEN
TO_NUMBER ( REGEXP_SUBSTR (t.from_ip,
'\w+',
1,
1)
|| LPAD (TO_NUMBER (REGEXP_SUBSTR (t.from_ip,
'\w+',
1,
2)), 3, 0)
|| LPAD (TO_NUMBER (REGEXP_SUBSTR (t.from_ip,
'\w+',
1,
3)), 3, 0)
|| LPAD (TO_NUMBER (REGEXP_SUBSTR (t.from_ip,
'\w+',
1,
4)), 3, 0)) and
TO_NUMBER ( REGEXP_SUBSTR (t.to_ip,
'\w+',
1,
1)
|| LPAD (TO_NUMBER (REGEXP_SUBSTR (t.to_ip,
'\w+',
1,
2)), 3, 0)
|| LPAD (TO_NUMBER (REGEXP_SUBSTR (t.to_ip,
'\w+',
1,
3)), 3, 0)
|| LPAD (TO_NUMBER (REGEXP_SUBSTR (t.to_ip,
'\w+',
1,
4)), 3, 0));
What I am doing is removing the dots, padding with 0's lpad(str,3,0), then concatenating and converting to number.
After converting to number doing comparison with between operator.
Want to hear any more efficient, sort-sweet-simple approach from you guys.
[EDITED] : I hope you understand how IP Address ranges are defined.
Manu
[Updated on: Fri, 12 September 2014 12:13] Report message to a moderator |
|
|
|
|
|
| Re: Fetch IP Addresses between given IP Range [message #623698 is a reply to message #623693] |
Fri, 12 September 2014 12:43  |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
As BlackSwan said... I use this formula:
ip_num = 16777216 * to_number(substr(ip,1,instr(ip,'.',1,1)-1))
+ 65536 * to_number(substr(ip,instr(ip,'.',1,1)+1,instr(ip,'.',1,2)-instr(ip,'.',1,1)-1))
+ 256 * to_number(substr(ip,instr(ip,'.',1,2)+1,instr(ip,'.',1,3)-instr(ip,'.',1,2)-1))
+ to_number(substr(ip,instr(ip,'.',1,3)+1)) |
|
|
|
|
|
| Re: Fetch IP Addresses between given IP Range [message #623706 is a reply to message #623705] |
Fri, 12 September 2014 14:19 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
manubatham20 wrote on Sat, 13 September 2014 00:36Replacing regex is fine
Regular expressions are resource intensive.
Quote:
but I don't see the use/benefit of multiplication/addition part, while my query don't require multiplication just for BETWEEN comparison.
A hint : IP address has 4 parts. Each part ranges from 0 - 255. So, if you look at Michel's query, you would find significant numbers for each part of the IP address, 1, 256, 256*256(65536), 65536*256(16777216).
|
|
|
|
|
|
| Re: Fetch IP Addresses between given IP Range [message #623708 is a reply to message #623705] |
Fri, 12 September 2014 14:29 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
manubatham20 wrote on Fri, 12 September 2014 21:06Replacing regex is fine, but I don't see the use/benefit of multiplication/addition part, while my query don't require multiplication just for BETWEEN comparison.
BlackSwan wrote on Fri, 12 September 2014 19:27...
IP# is a simple 32-bit (4 bytes) integer; which is being presented in "dotted quad" format.
Each byte will contain a value between 0 and 255...
The expression just convert the string to the real IP value.
You can also use:
lpad(substr(ip,1,instr(ip,'.',1,1)-1),3,'0')
|| lpad(substr(ip,instr(ip,'.',1,1)+1,instr(ip,'.',1,2)-instr(ip,'.',1,1)-1),3,'0')
|| lpad(substr(ip,instr(ip,'.',1,2)+1,instr(ip,'.',1,3)-instr(ip,'.',1,2)-1),3,'0')
|| lpad((substr(ip,instr(ip,'.',1,3)+1),3,'0')
It depends on how your IP is recorded in your table.
[Updated on: Fri, 12 September 2014 14:30] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Fetch IP Addresses between given IP Range [message #623719 is a reply to message #623708] |
Fri, 12 September 2014 23:59 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
LPAD + SUBSTR + INSTR might be a bit faster, but REGEXP_REPLACE + REGEXP_REPLACE is a bit shorter:
regexp_replace(regexp_replace(ip,'(\d+)','000\1'),'\d+(\d{3})','\1')
For example:
SQL> select regexp_replace(regexp_replace('10.250.2.0','(\d+)','000\1'),'\d+(\d{3})','\1')
2 from dual
3 /
REGEXP_REPLACE(
---------------
010.250.002.000
SQL>
So whole thing would be:
WITH temp AS (
SELECT '010.250.002.000' from_ip,
'010.251.001.254' to_ip
FROM DUAL
)
SELECT R.IP_DISPLAY_V4
FROM table_with_ip_address r,
temp t
WHERE REGEXP_REPLACE(REGEXP_REPLACE(R.IP_DISPLAY_V4,'(\d+)','000\1'),'\d+(\d{3})','\1') BETWEEN from_ip
AND to_ip
/
SY.
[Updated on: Sat, 13 September 2014 14:18] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Thu Apr 25 14:23:38 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
