Johan,
Here is a procedure that I use to break up a comma delimited string into
it's component pieces. It should be a simple thing to replace the ',' with a
'.' and use it to do as you need. You'll have to call it 4 times for each IP
address and then convert the result to a number, but that is not very hard.
procedure extract_next(str in varchar2, position in out number, result out
varchar2) is
a number;
begin
a := instr(substr(str,position),',',1);
if(a = 0) then a := length(substr(str, position));
end if;
result := substr(str, position, instr(substr(str,position),',',1)-1);
a := instr(substr(str,position),',',1);
if(a = 0) then position := length(str)+1;
else position := position + a;
end if;
end;
str = the original string ('127.0.0.1')
position is a number that holds the last location where a ',' or '.' in your
case was found. It should start at 0.
result is the piece of the string between the ','s or '.'s
Dick Goulet
PS: no warranty, guarantee, or fitness for use implied. Use at your OWN risk.
____________________Reply Separator____________________
Author: Johan Muller <[EMAIL PROTECTED]>
Date: 10/16/2002 12:32 PM
Help!
Anybody have a quick and dirty to parse the 4 octets of a typical IP address
into 4 separate values. I will insert these into a table where database
checks may verify that the data is in fact a number and also part of a
valid ip range (the second thru fourth octets cannot be higher than 255. The
source data is very dirty and often fat-fingered, hence the painful
solution):
e.g.: 127.0.0.1 into 127 (val 1), 0 (val 2), 0 (val 3) and 1 (val 4).
I have used various flavors of substr/instr to unravel this, but the varying
length of the octets (up to 3 bytes) defeats my rudimentary sql coding
skills. I probably have to attack the IP with decode, and any input will be
very welcome.
Running V 8.1.6.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Johan Muller
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
