RE: Sql question : use of SUBSTR/INSTR functions

From: Mirsky, Greg <gmirsky_at_Estee.com>
Date: Wed, 16 Oct 2002 13:26:31 -0800
Message-ID: <F001.004EB5E3.20021016132631@fatcity.com>

<<Anybody have a quick and dirty to parse the 4 octets of a typical IP address>>

How about this...

FUNCTION f_ip_to_number (

   p_ip                                NVARCHAR2

)

   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: gmirsky_at_Estee.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:26:31 CDT