Thursday, September 26, 2013

Oracle PLSQL string tokenizer package procedure to break a string into tokens based on a delimiter



--Following package procedure is used to break a string into substrings or tokens based on a delimiter
CREATE PACKAGE BODY xx_token_pkg
 AS   TYPE token_tbl IS TABLE OF VARCHAR2 (2000)
INDEX BY BINARY_INTEGER;

PROCEDURE string_tokenizer (
                                      p_string        IN        VARCHAR2,
                                      p_delimiter    IN        VARCHAR2,
                                      p_result_tbl   OUT     xx_ar_billing_ext_pkg.token_tbl
                                           )
   IS

      CURSOR c_tokens
      IS
         SELECT     LEVEL,
                    SUBSTR (string_to_tokenize,
                            DECODE (LEVEL,
                                    1, 1,
                                      INSTR (string_to_tokenize,
                                             delimiter,
                                             1,
                                             LEVEL - 1
                                            )
                                    + 1
                                   ),
                              INSTR (string_to_tokenize, delimiter, 1, LEVEL)
                            - DECODE (LEVEL,
                                      1, 1,
                                        INSTR (string_to_tokenize,
                                               delimiter,
                                               1,
                                               LEVEL - 1
                                              )
                                      + 1
                                     )
                           ) token
               FROM (SELECT p_string || p_delimiter AS string_to_tokenize,
                            p_delimiter AS delimiter
                       FROM DUAL)
         CONNECT BY INSTR (string_to_tokenize, delimiter, 1, LEVEL) > 0
         ORDER BY LEVEL ASC;

      l_index   BINARY_INTEGER := 0;

   BEGIN

      FOR token_rec IN c_tokens
      LOOP
         p_result_tbl (l_index) := token_rec.token;
         l_index := l_index + 1;
      END LOOP;

   EXCEPTION
     WHEN OTHERS THEN
       print_log('Exception in string_tokenizer'||sqlerrm);
   END string_tokenizer;

END xx_token_pkg;


--Following is an anonymous block which calls xx_token_pkg.string_tokenizer to break the string into substrings/tokens using a separater/delimiter '-'DECLARE
   l_result   xx_token_pkg.token_tbl;
BEGIN
   xx_token_pkg.string_tokenizer ('Test1-Test2-Test3', '-', l_result);
   FOR i IN l_result.FIRST .. l_result.LAST
   LOOP
      DBMS_OUTPUT.put_line ('result=' || l_result (i));
   END LOOP;
END;
Running the above blcok results in the following output:-

result=Test1
result=Test2
result=Test3

No comments:

Post a Comment