08 July, 2011

How to make split string separated by specific character

SA,
Today I will present plsql function that do solution for splitting string separated by specific character.
for example If I have string 'year,month,day,hour,minute,second' its elements separated by comma and We want the output to be like the following
year
month
day
hour
minute
second



The following function execute the requested in previous example :
/******************************************************************************
/*author           : Mahmoud Ahmed El-sayed
/*creation date    : 08/07/2011
/*Function Purpose : That function used to split string separeted by specific
/*                   character to set of elements
/*$parameters      :
/*         in_string      ==> your input string
/*         in_position    ==> element serial in string series
/*         in_seperator   ==> seperator character
/******************************************************************************/

CREATE OR REPLACE FUNCTION SPLIT (
   in_string      IN   VARCHAR2,                               -- input string
   in_position    IN   PLS_INTEGER,         -- element serial in string series
   in_seperator   IN   VARCHAR2 DEFAULT ','             -- separator character
)
   RETURN VARCHAR2
IS
   l_string      VARCHAR2 (32767) := in_seperator || in_string;
   l_pos_first   PLS_INTEGER;
   l_pos_last    PLS_INTEGER;
BEGIN
   l_pos_first := INSTR (in_string, in_seperator, 1, in_position);

   IF l_pos_first > 0
   THEN
      l_pos_last := INSTR (in_string, in_seperator, 1, in_position + 1);

      IF l_pos_last = 0
      THEN
         l_pos_last := LENGTH (in_string) + 1;
      END IF;

      RETURN (SUBSTR (in_string, l_pos_first + 1,
                      l_pos_last - l_pos_first - 1)
             );
   ELSE
      RETURN NULL;
   END IF;
END;
/

Now we can make plsql block to test that function
DECLARE
   l_string    VARCHAR2 (50)  := 'year,month,day,hour,minute,second';
   l_element   VARCHAR2 (100);
   i           PLS_INTEGER    := 1;
BEGIN
   LOOP
      l_element := SPLIT (l_string, i, ',');
      EXIT WHEN l_element IS NULL;
      DBMS_OUTPUT.put_line (l_element);
      i := i + 1;
   END LOOP;
END;

The output is
month
day
hour
minute
second

Thanks
Recommended Post Slide Out For Blogger