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 [EMAIL PROTECTED] 212-604-0200 x106 On 10/16/2002 4:32 PM, Johan Muller <[EMAIL PROTECTED]> 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: [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: Alan Davey 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).