Home » SQL & PL/SQL » SQL & PL/SQL » Procedure to validate an IP Address
Procedure to validate an IP Address [message #185628] Wed, 02 August 2006 14:38 Go to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
Has anyone coded up a procedure/function that validates an IP Address? I'm just wondering if anyone has done something like that.

Valid IP Address
180.111.180.2

Invalid IP Address
134.4.3.180.2
1234.24.180.2
Re: Procedure to validate an IP Address [message #185637 is a reply to message #185628] Wed, 02 August 2006 15:58 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
If you are on 10g, the following will work:

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?|
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
Re: Procedure to validate an IP Address [message #185640 is a reply to message #185628] Wed, 02 August 2006 16:14 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
Hey thanks! Looks like I need to do a little reading on "regexp_instr". I've never used that before.
Re: Procedure to validate an IP Address [message #185641 is a reply to message #185628] Wed, 02 August 2006 16:22 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
Would it be possible for you to give me a quick break down of some of the components?

([01]?\d\d?|2[0-4]\d|25[0-5])\
Re: Procedure to validate an IP Address [message #185645 is a reply to message #185641] Wed, 02 August 2006 18:08 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Sure...for example, for 198.162.0.1 we'll verify the 198 with the first sequence of [01]?\d\d?|2[0-4]\d|25[0-5])\

The three digit number (ie. 198) can be in one of the following patterns:

25[0-5] --> 25 followed by a number between 0 - 5 (Verifies that the number can't be greater than 255)
2[0-4]\d --> 2 followed by a number 0 - 4 and then any digit between 0 - 9
[01]?\d\d? --> checks the following formats:
  • if the number is three digits, it must begin with 0 or 1 followed by two digits between 0 - 9
  • if the number is 1 or 2 digits, then both numbers can be any digit between 0 - 9

? --> zero or one of the preceding subexpression
\d --> digit character

the \ at the end is simply escaping the decimal '.'

The regular expression does not check for 0.0.0.0, however it can be modified to accommodate for it as well.

Hope this helps.
Previous Topic: finding a charachter in a string
Next Topic: Query help
Goto Forum:
  


Current Time: Fri Dec 09 03:43:23 CST 2016

Total time taken to generate the page: 0.08666 seconds