RE: Sql question : use of SUBSTR/INSTR functions
<> How about this... FUNCTION f_ip_to_number ( p_ipNVARCHAR2 ) RETURN NUMBER IS v_ip_segment1 NUMBER := SUBSTR (p_ip, 1, INSTR (p_ip, '.') - 1); v_ip_segment2 NUMBER := SUBSTR ( p_ip , INSTR (p_ip, '.', 1, 1) + 1 , INSTR (p_ip, '.', 1, 2) - INSTR (p_ip, '.', 1, 1) - 1 ); v_ip_segment3 NUMBER := SUBSTR ( p_ip , INSTR (p_ip, '.', 1, 2) + 1 , INSTR (p_ip, '.', 1, 3) - INSTR (p_ip, '.', 1, 2) - 1 ); v_ip_segment4 NUMBER := SUBSTR (p_ip, INSTR (p_ip, '.', -1) + 1); BEGIN RETURN ( ( (v_ip_segment1 * 256 + v_ip_segment2) * 256 ) + v_ip_segment3 ) * 256 + v_ip_segment4; END f_ip_to_number; -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mirsky, Greg 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).
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 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).
Re: Sql question : use of SUBSTR/INSTR functions
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).
RE: Sql question : use of SUBSTR/INSTR functions
Try this. It uses the INSTR function to determine the start and end of the
SUBSTR.
1 select substr('333.22.1.000',1,instr('333.22.1.000','.')-1) octet1,
2 substr('333.22.1.000',
3(instr('333.22.1.000','.',1,1) + 1),
4(instr('333.22.1.000','.',1,2) -
instr('333.22.1.000','.',1,1))-1) octect2,
5 substr('333.22.1.000',
6(instr('333.22.1.000','.',1,2) + 1),
7(instr('333.22.1.000','.',1,3) -
instr('333.22.1.000','.',1,2))-1) octect3,
8 substr('333.22.1.000',(instr('333.22.1.000','.',1,3) + 1))
octect3
9* from dual
SQL> /
OCT OC O OCT
--- -- - ---
333 22 1 000
Dan Fink
-Original Message-
Sent: Wednesday, October 16, 2002 2: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: Fink, Dan
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).
