Home » SQL & PL/SQL » SQL & PL/SQL » Playing With IP (Merged)
Playing With IP (Merged) [message #217854] Mon, 05 February 2007 21:40 Go to next message
cacheboy
Messages: 21
Registered: August 2006
Junior Member
Just want to check if there are any ways to validate an IP. I mean are there any build-in packages/libraries to do this? And if so, is it only for IPv4 or also supports IPv6 (for the future but not that critical now).

I wrote my own in Java but do not know how to convert it to PL/SQL equipvalent. Maybe using Regex might help.
Re: Playing With IP 1 [message #217857 is a reply to message #217854] Mon, 05 February 2007 21:45 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
If you are on 10g, this will work for IPv4.

SQL> select address,
  2  decode (regexp_instr(address,'^([01]?\d\d?|2[0-4]\d|25[0-5])\.([01]?\d\d?|2[0-4]\d|25[0-5])\.([01]?\d\d?|
  3  2[0-4]\d|25[0-5])\.([01]?\d\d?|2[0-4]\d|25[0-5])$'), 1, 'Valid', 'NOT-Valid') Status from test_ip;

ADDRESS          STATUS
---------------- ---------
180.111.180.2    Valid
134.4.3.180.2    NOT-Valid
1234.24.180.2    NOT-Valid
icon5.gif  Playing With IP 2 [message #217858 is a reply to message #217854] Mon, 05 February 2007 21:46 Go to previous messageGo to next message
cacheboy
Messages: 21
Registered: August 2006
Junior Member
Ok I want to check if a certain IP is within a certain range. Again are there any available packages or libraries.

If I were to code my own. I was thinking of breaking the IP (talking about IPv4. IPv6... will crack my head for a later time) into the 4 octets. And maybe check that the 1st 3 octets are the same and the 4th octet is within a certain range. Will this do?
icon9.gif  Re: Playing With IP 1 [message #217859 is a reply to message #217854] Mon, 05 February 2007 21:56 Go to previous messageGo to next message
cacheboy
Messages: 21
Registered: August 2006
Junior Member
Thanks it looks workable. Can't try it as I am on Oracle 9.1.2 =(
Re: Playing With IP 1 [message #217892 is a reply to message #217859] Tue, 06 February 2007 00:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
No need to start a new topic. Please continue in the same thread.
Re: Playing With IP 1 [message #218023 is a reply to message #217857] Tue, 06 February 2007 07:56 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
ebrian wrote on Mon, 05 February 2007 22:45
If you are on 10g, this will work for IPv4.



I have been wanting to say this for a long time ebrian. With all these examples of regular expressions from you, I am learning a lot more than on my own. Thanks.
Re: Playing With IP 1 [message #218037 is a reply to message #218023] Tue, 06 February 2007 09:08 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Thanks joy_division, I appreciate the comment. I'm glad I can help.

It definitely helps to have the poster's questions to practice with using regex's. There are many times when what would appear to be trivial issue to solve with regex, gets ya going in circles !!
Previous Topic: Connect by Prior - Performance Issue
Next Topic: time dimension loading week startign from saturday
Goto Forum:
  


Current Time: Sat Dec 10 04:58:58 CST 2016

Total time taken to generate the page: 0.04360 seconds