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!

REGEXP_REPLACE / REGEXP_LIKE for Oracle 9i

I really love using the REGEXP_REPLACE function and REGEXP_LIKE operator in Oracle 10G - and have been spoiled by their ease of use as a result.

One of my clients, however - is sort of "stuck" on Oracle 9iR2 due to regression testing cost requirements of an upgrade. The problem is that we still have to do development on the 9iR2 database - and I REALLY want my regular expressions!

So - I figured that I would take a stab at writing my own REGEXP_REPLACE and REGEXP_LIKE functions.

Here's the source code - in case you want the same functionality in Oracle 9iR2:

create or replace
FUNCTION regexp_replace (
   source_char       IN   VARCHAR2
 , pattern           IN   VARCHAR2
 , replace_string    IN   VARCHAR2
 , POSITION          IN   PLS_INTEGER DEFAULT 1
 , occurrence        IN   PLS_INTEGER DEFAULT 0
 , match_parameter   IN   VARCHAR2 DEFAULT NULL)
   RETURN VARCHAR2 AS
/*************************************************************************
 * Program  : regexp_replace
 * Version  : 1.0
 * Author   : Philip Moore
 * Date     : 20-JUN-2009 Anno Domini
 * Purpopse : This provides a pseudo "REGEXP_REPLACE" function for Oracle 9iR2
 * Warnings : Do NOT compile in an Oracle 10GR2 database (or above)!
 *************************************************************************/
   -- Variables
   l_source_string                    VARCHAR2 (32767);
   l_temp_string                      VARCHAR2 (32767);
   l_flags                            VARCHAR2 (10);
   l_occurrence                       PLS_INTEGER;
   l_end_of_pattern_pos               PLS_INTEGER;
   l_string_pos                       PLS_INTEGER;
BEGIN
   -- Substr the source_char to start at the position specified
   l_source_string := SUBSTR (source_char, POSITION);

   -- Set up the flags argument
   IF occurrence = 0 THEN
      l_flags := 'g';
   ELSE
      l_flags := NULL;
   END IF;

   l_flags := l_flags || match_parameter;

   -- Now replace the regular expression pattern globally if "g"
   IF INSTR (l_flags, 'g') > 0 THEN
      owa_pattern.CHANGE (line          => l_source_string
                        , from_str      => pattern
                        , to_str        => replace_string
                        , flags         => l_flags);
   -- Not a global replace - loop until the "occurrence"th occurrence is replaced...
   ELSE
      l_string_pos := 0;
      l_occurrence := 0;
      WHILE l_string_pos < LENGTH (l_source_string) AND l_occurrence < occurrence LOOP
         l_string_pos := l_string_pos + 1;

         l_end_of_pattern_pos := owa_pattern.amatch (line          => l_source_string
                                                   , from_loc      => l_string_pos
                                                   , pat           => pattern
                                                   , flags         => match_parameter);

         IF l_end_of_pattern_pos != 0 THEN
            l_occurrence := l_occurrence + 1;
         END IF;
      END LOOP;

      IF l_occurrence = occurrence THEN
         l_temp_string := SUBSTR (l_source_string
                                , l_string_pos
                                , (l_end_of_pattern_pos - l_string_pos));
         owa_pattern.CHANGE (line          => l_temp_string
                           , from_str      => pattern
                           , to_str        => replace_string
                           , flags         => l_flags);
         l_source_string := SUBSTR (l_source_string
                                  , 1
                                  , l_string_pos - 1) || l_temp_string || SUBSTR (l_source_string, l_end_of_pattern_pos);
      END IF;
   END IF;

   -- Piece the string back together if needed...
   IF POSITION > 1 THEN
      l_source_string := SUBSTR (source_char
                               , 1
                               , (POSITION - 1)) || l_source_string;
   END IF;

   RETURN l_source_string;
END regexp_replace;
/


CREATE OR REPLACE FUNCTION regexp_like (source_char     IN VARCHAR2
                                      , pattern         IN VARCHAR2
                                      , match_parameter IN VARCHAR2 DEFAULT NULL)
RETURN INTEGER
AS
/*************************************************************************
 * Program  : regexp_like
 * Version  : 1.0
 * Author   : Philip Moore
 * Date     : 20-JUN-2009 Anno Domini
 * Purpopse : This provides a pseudo "REGEXP_LIKE" operator for Oracle 9iR2
 * Warnings : Do NOT compile in an Oracle 10GR2 database (or above)!
 *************************************************************************/
  -- Variables
  l_return INTEGER;
BEGIN
   IF owa_pattern.match (line  => source_char
                       , pat   => pattern
                       , flags => match_parameter) THEN
      l_return := 1;
   ELSE
      l_return := 0;
   END IF;

   RETURN l_return;
END regexp_like;
/

The Functions in Action:
Here's a demo of my new Oracle 9iR2 REGEXP_REPLACE function:

SELECT regexp_replace ('5138675309', '(\d{3})(\d{3})(\d{4})', '(\1) \2-\3') AS phone 
FROM v$version 
WHERE banner = 'Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production'; 

Result: 
PHONE 
---------------- 
(513) 867-5309 

Some Watchouts:
The "OWA_PATTERN" package isn't as powerful as the built-in REGEXP% functions that Oracle 10g Provides - for example - it doesn't support the "" OR operator. It is also much slower than those natively built-in 10g functions - so don't use these to find the last digit in pi :)

I hope these are helpful in your "stuck in Oracle 9i" endeavors!

Special thanks to Greg Houston's http://formatmysourcecode.blogspot.com/ for helping me format the code for blogger!