5 Replies Latest reply on Dec 3, 2011 9:28 AM by 804386

    split a string in pl/sql

    844722
      Hi

      How to split a string and store it in associative array in pl/sql.function

      my string like this

      '1102,1101,1012,1011,1010,1009,1008,1007,1006,10005,1004,1003,1002,1001'
      and assign to associative array list. in function
      can anyone please help

      regards
      r
        • 1. Re: split a string in pl/sql
          327282
          You can try the following. Although I would advise it would be better to write the tokenizer function as a java stored procedure and use the result. We have a well-defined string tokenizer class in Java.
          -- Define function to split string into tokens
          FUNCTION get_token(
              p_input_string IN VARCHAR2,            -- input string
              p_token_number IN PLS_INTEGER,         -- token number
              p_delimiter    IN VARCHAR2 DEFAULT ',' -- separator character
            )
            RETURN VARCHAR2
          IS
            v_temp_string VARCHAR2(32767) := p_delimiter || p_input_string ;
            v_pos1 PLS_INTEGER ;
            v_pos2 PLS_INTEGER ;
          BEGIN
            v_pos1     := INSTR( v_temp_string, p_delimiter, 1, p_token_number ) ;
            IF v_pos1   > 0 THEN
              v_pos2   := INSTR( v_temp_string, p_delimiter, 1, p_token_number + 1) ;
              IF v_pos2 = 0 THEN
                v_pos2 := LENGTH( v_temp_string ) + 1 ;
              END IF ;
              RETURN( SUBSTR( v_temp_string, v_pos1+1, v_pos2 - v_pos1-1 ) ) ;
            ELSE
              RETURN NULL ;
            END IF ;
          EXCEPTION
            WHEN OTHERS THEN
              RAISE;      
          END get_token;
          
          -- Call the above function in loop for a string with N tokens
          DECLARE
                TYPE assoc_arr_str_typ IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
                str_arr assoc_arr_str_typ;
          
                v_str VARCHAR2(200) := '1102,1101,1012,1011,1010,1009,1008,1007,1006,10005,1004,1003,1002,1001' ;
                v_token   VARCHAR2(4) ;
                i          PLS_INTEGER := 1 ;  
              BEGIN
                LOOP
                  v_token := get_token( v_str, i , ',') ;
                  EXIT WHEN v_token IS NULL ;
                  dbms_output.put_line( v_token ) ;
                  str_arr(i) := v_token;
                  i := i + 1 ;
               END LOOP ;
            END ;
           /
          Edited by: GG on Mar 24, 2011 9:51 AM
          • 2. Re: split a string in pl/sql
            844722
            thanks yaar
            regardsr
            • 3. Re: split a string in pl/sql
              842402
              If you are using 10G....

              DECLARE
              var1 VARCHAR2 (100)
              := '1102,1101,1012,1011,1010,1009,1008,1007,1006,10005,1004,1003,1002,1001';

              TYPE t1 IS TABLE OF NUMBER
              INDEX BY PLS_INTEGER;

              v_t1 t1;
              BEGIN
              FOR i IN 1 .. LENGTH (var1)
              LOOP
              v_t1 (i) := REGEXP_SUBSTR (var1, '[^,]+', 1, i);
              EXIT WHEN v_t1 (i) IS NULL;
              DBMS_OUTPUT.put_line ('v_t1[' || i || '] : ' || v_t1 (i));
              END LOOP;
              END;
              1 person found this helpful
              • 4. Re: split a string in pl/sql
                Sayan Malakshinov.
                You can see example in my package xt_regexp https://github.com/xtender/XT_REGEXP

                Regards,
                Malakshinov Sayan
                1 person found this helpful
                • 5. Re: split a string in pl/sql
                  804386
                  Here is another simple method for splitting string in plsql using NESTED TABLE.

                  --First create a type named TOKENS
                  create or replace TYPE tokens AS TABLE OF varchar2(100);
                  /
                  --Second create a StringTokenizer function which takes a String and a delimiter as a input parameter
                  create or replace
                  function StringTokenizer(str varchar2, delim CHAR) return tokens
                  is
                  toks tokens;
                  ch varchar2(1) := substr(str,1,1);
                  tempStr varchar2(100) :='';
                  index2 integer :=1;
                  ctr integer := 2;
                  begin
                  toks := tokens(1);
                  toks.extend(length(str) );
                  while ch is not null loop
                  if ch = delim then
                  toks(index2) := tempStr;
                  tempStr :='';
                  index2 := index2 + 1;
                  ch := substr(str,ctr,1);
                  ctr := ctr + 1;
                  else
                  tempStr := tempStr||ch;
                  ch := substr(str,ctr,1);
                  ctr := ctr + 1;
                  end if;
                  end loop;
                  if length(tempStr) > 0 then
                  toks(index2) := tempStr;
                  end if;
                  return toks;
                  end;
                  /
                  --Now to use the StringTokenizer function, here the delimiter is | symbol
                  set serveroutput on;
                  declare
                  tok tokens ;
                  begin
                  tok := StringTokenizer('The|quick|brown|fox|jumps|over|a|little|lazy|dog','|');

                  for i in tok.first..tok.count loop
                  if tok(i) is not null then
                  dbms_output.put_line(tok(i));
                  end if;
                  end loop;
                  end;