Oracle-L: Re:Sql question : use of SUBSTR/INSTR functions

From: <dgoulet_at_vicr.com>
Date: Wed, 16 Oct 2002 13:05:44 -0800
Message-ID: <F001.004EB4EC.20021016130544@fatcity.com>

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 <c-johan.muller_at_wcom.com>
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: c-johan.muller_at_wcom.com

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: ListGuru_at_fatcity.com (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: dgoulet_at_vicr.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------