Monday, 4 February 2013

Parse String Or Split String Using Delimiter


IF u have A STRING which has x no.OF comma separated VALUES, AND now u want TO put those comma separated VALUES INTO A SINGLE COLUMN rows.
U can achieve this BY below steps:

Fallow THE steps:

1. Create an array.

CREATE OR REPLACE TYPE ep_varchar2_array IS TABLE OF VARCHAR2 (4000);

2.Create a table.

CREATE TABLE test_x (col1 VARCHAR2 (10));


3. Create a function.

CREATE OR REPLACE FUNCTION parse_string (i_string      IN VARCHAR2,
                                         i_delimiter   IN VARCHAR2)
   RETURN ep_varchar2_array
AS
   x_counter     NUMBER;
   x_position    NUMBER;
   x_string      VARCHAR2 (32767) := i_string;
   x_len         NUMBER := LENGTH (i_delimiter);
   x_out_array   ep_varchar2_array := ep_varchar2_array (NULL);
BEGIN
   x_counter := 1;

   IF (i_delimiter = SUBSTR (x_string, 1, x_len))
   THEN
      x_string := SUBSTR (x_string, x_len + 1);
   END IF;

   IF (i_delimiter = SUBSTR (x_string, 0 - x_len))
   THEN
      x_string := SUBSTR (x_string, 1, LENGTH (x_string) - x_len);
   END IF;

   LOOP
      x_position := INSTR (x_string, i_delimiter);

      IF (NVL (x_position, 0) > 1)
      THEN
         x_out_array (x_counter) := SUBSTR (x_string, 1, x_position - 1);

         IF (x_position < LENGTH (x_string))
         THEN
            x_string :=
               SUBSTR (x_string,
                       x_position + x_len,
                       LENGTH (x_string) - x_position);
         ELSIF (NVL (x_position, 0) = LENGTH (x_string))
         THEN
            x_counter := x_counter + 1;
            x_out_array (x_counter) := NULL;
            EXIT;
         END IF;
      ELSIF (NVL (x_position, 0) = 1)
      THEN
         x_out_array (x_counter) := NULL;

         IF (x_position < LENGTH (x_string))
         THEN
            x_string :=
               SUBSTR (x_string,
                       x_position + x_len,
                       LENGTH (x_string) - x_position);
         ELSIF (NVL (x_position, 0) = LENGTH (x_string))
         THEN
            x_counter := x_counter + 1;
            x_out_array (x_counter) := NULL;
            EXIT;
         END IF;
      ELSIF (NVL (x_position, 0) = 0)
      THEN
         x_out_array (x_counter) := x_string;
         EXIT;
      END IF;

      x_counter := x_counter + 1;
      x_out_array.EXTEND;
   END LOOP;

   RETURN x_out_array;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN x_out_array;
END parse_string;
/

4.after creation u can use function like below:

insert into test_x select * from table(parse_string ('a,b,1,e,f',','));

5. I f u want to view those values from string 

select * from table(parse_string ('a,b,1,e,f',','));

column_value
----------------
a
b
1
e
f