| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Sql question : use of SUBSTR/INSTR functions
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 - psherman_at_elcom.com
-----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: 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).
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). Received on Wed Oct 16 2002 - 16:19:23 CDT
 |
 |