Handy Function for parsing strings in PL/SQL!

One common function needed for PL/SQL is some sort of string parsing solution. For instance, if you want to parse out the "3rd" position of a string delimited by an "/" character.

My buddy Todd had written a handy function in Visual Basic for Applications, and even converted it for use in Oracle. After I saw Tom Kyte use a combination of INSTR and SUBSTR to parse strings, I rewrote Todd's approach to perform a little better.

Here's the code in case you are interested in this handy little string function:

CREATE OR REPLACE FUNCTION parsestring (
   p_string      IN   VARCHAR2
 , p_delimiter   IN   VARCHAR2
 , p_position    IN   INTEGER
)
/**************************************************************
  Function: parsestring

  Version:  2.0

  Author:  Philip Moore - (version 2.0)

  Original Author: Todd Ward - (version 1.0)
  
  Date: 23-JAN-2006
  
  Description: This function's purpose is to return
               the "p_position"th portion of the string
               "p_string", delimited by "p_delimiter".
               
  Example call: parsestring('a/b/c', '/', 2) Returns: 'b'
**************************************************************/
RETURN VARCHAR2     
AUTHID CURRENT_USER
PARALLEL_ENABLE
DETERMINISTIC
AS
-- Variables
   v_txt      VARCHAR2 (32767);
BEGIN
   v_txt := p_delimiter || p_string || p_delimiter;
   RETURN SUBSTR (v_txt
                , INSTR (v_txt
                       , p_delimiter
                       , 1
                       , p_position
                        ) + LENGTH (p_delimiter)
                , INSTR (v_txt
                       , p_delimiter
                       , 1
                       , p_position + 1
                        ) - INSTR (v_txt
                                 , p_delimiter
                                 , 1
                                 , p_position
                                  ) - LENGTH (p_delimiter)
                 );
EXCEPTION
   WHEN OTHERS THEN
      RETURN NULL;
END parsestring;
/

SHOW ERRORS;

Now - note that this will not perform as well as using the SUBSTR and INSTR combination of functions shown in the source code itself - because of the PL/SQL context switches when run in the SQL engine. But I personally think that it is MUCH easier to remember the syntax for calling "PARSESTRING" than for calling the complex SUBSTR/INSTR combination.

I've used this function many thousands of times over the years - and I believe that you will find it handy as well.

Good luck!