Hello, Try this (take a hard look first, as I cranked this out quickly while doing other things):
substr(ip_addr,1,instr(ip_addr,'.',1,1)-1 <get 1st octet substr(ip_addr,1,instr(ip_addr,'.',instr(ip_addr,'.',1,1)+1,1)-1 <get 2nd octet substr(ip_addr,1,instr(ip_addr,'.',instr(ip_addr,'.',1,2)+1,1)-1 <get 3rd octet substr(ip_addr,1,instr(ip_addr,'.',instr(ip_addr,'.',1,3)+1,1) <get 4th octet Thank you, Paul Sherman DBA Elcom, Inc. voice - 781-501-4143 (direct #) fax - 781-278-8341 (secure) email - [EMAIL PROTECTED] -----Original Message----- Sent: Wednesday, October 16, 2002 4:32 PM To: Multiple recipients of list ORACLE-L 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: Sherman, Paul R. 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).
