Hi Johan,
Try this:
SELECT SUBSTR('127.0.0.1',1,INSTR('127.0.0.1','.')-1)
,SUBSTR('127.0.0.1',INSTR('127.0.0.1','.')+1,INSTR('127.0.0.1','.',1,2)-(INSTR('127.0.0.1','.')+1))
,SUBSTR('127.0.0.1',INSTR('127.0.0.1','.',1,2)+1,INSTR('127.0.0.1','.',1,3)-(INSTR('127.0.0.1','.',1,2)+1))
,SUBSTR('127.0.0.1',INSTR('127.0.0.1','.',1,3)+1,LENGTH('127.0.0.1')-INSTR('127.0.0.1','.',1,3)+1)
FROM DUAL
There may be a more elegant solution, but this was the quickest I could come up with.
--
Alan Davey
adavey_at_competitrack.com
212-604-0200 x106
On 10/16/2002 4:32 PM, Johan Muller <c-johan.muller_at_wcom.com> wrote:
>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: Alan Davey
INET: adavey_at_competitrack.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).
Received on Wed Oct 16 2002 - 16:05:45 CDT
