Is there a function to split a string in PL/SQL?

Question

I need to write a procedure to normalize a record that have multiple tokens concatenated by one char. I need to obtain these tokens splitting the string and insert each one as a new record in a table. Does Oracle have something like a "split" function?

Answer 1

Please find next an example you may find useful

--1st substring

select substr('alfa#bravo#charlie#delta', 1,  
  instr('alfa#bravo#charlie#delta', '#', 1, 1)-1) from dual;

--2nd substring

select substr('alfa#bravo#charlie#delta', instr('alfa#bravo#charlie#delta', '#', 1, 1)+1,  
  instr('alfa#bravo#charlie#delta', '#', 1, 2) - instr('alfa#bravo#charlie#delta', '#', 1, 1) -1) from dual;

--3rd substring

select substr('alfa#bravo#charlie#delta', instr('alfa#bravo#charlie#delta', '#', 1, 2)+1,  
  instr('alfa#bravo#charlie#delta', '#', 1, 3) - instr('alfa#bravo#charlie#delta', '#', 1, 2) -1) from dual;

--4th substring

select substr('alfa#bravo#charlie#delta', instr('alfa#bravo#charlie#delta', '#', 1, 3)+1) from dual;

Best regards

Emanuele

Answer 2

There is apex_util.string_to_table - see my answer to this question.

Also, prior to the existence of the above function, I once posted a solution here on my blog.

Update

In later versions of APEX, apex_util.string_to_table is deprecated, and a similar function apex_string.split is preferred.

Answer 3

If APEX_UTIL is not available, you have a solution using REGEXP_SUBSTR().

Inspired from http://nuijten.blogspot.fr/2009/07/splitting-comma-delimited-string-regexp.html :

DECLARE
  I INTEGER;
  TYPE T_ARRAY_OF_VARCHAR IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
  MY_ARRAY T_ARRAY_OF_VARCHAR;
  MY_STRING VARCHAR2(2000) := '123,456,abc,def';
BEGIN
  FOR CURRENT_ROW IN (
    with test as    
      (select MY_STRING from dual)
      select regexp_substr(MY_STRING, '[^,]+', 1, rownum) SPLIT
      from test
      connect by level <= length (regexp_replace(MY_STRING, '[^,]+'))  + 1)
  LOOP
    DBMS_OUTPUT.PUT_LINE(CURRENT_ROW.SPLIT);
    MY_ARRAY(MY_ARRAY.COUNT) := CURRENT_ROW.SPLIT;
  END LOOP;
END;
/

Answer 4

You can use regexp_substr(). Example:

create or replace type splitTable_Type is table of varchar2(100);

declare
    l_split_table splitTable_Type;
begin
  select
      regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level)
  bulk collect into
      l_split_table
  from dual
  connect by
      regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null;
end;

The query iterates through the comma separated string, searches for the comma (,) and then splits the string by treating the comma as delimiter. It returns the string as a row, whenever it hits a delimiter.

level in statement regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) refers to a pseudocolumn in Oracle which is used in a hierarchical query to identify the hierarchy level in numeric format: level in connect by

Answer 5

function numinstr(p_source in varchar2,p_token in varchar2)
return pls_integer
is
    v_occurrence pls_integer := 1;
    v_start pls_integer := 1;
    v_loc pls_integer;
begin
    v_loc:=instr(p_source, p_token, 1, 1);
    while v_loc > 0 loop
      v_occurrence := v_occurrence+1;
      v_start:=v_loc+1;
      v_loc:=instr(p_source, p_token, v_start, 1);
    end loop;
    return v_occurrence-1;
end numinstr;
  --
  --
  --
  --
function get_split_field(p_source in varchar2,p_delim in varchar2,nth pls_integer)
return varchar2
is
    v_num_delims pls_integer;
    first_pos pls_integer;
    final_pos pls_integer;
    len_delim pls_integer := length(p_delim);
    ret_len pls_integer;
begin
    v_num_delims := numinstr(p_source,p_delim);
    if nth < 1 or nth > v_num_delims+1 then
      return null;
    else
      if nth = 1 then
        first_pos := 1;
      else
        first_pos := instr(p_source, p_delim, 1, nth-1) + len_delim;
      end if;
      if nth > v_num_delims then
        final_pos := length(p_source);
      else
        final_pos := instr(p_source, p_delim, 1, nth) - 1;
      end if;
      ret_len := (final_pos - first_pos) + 1;
      return substr(p_source, first_pos, ret_len);
    end if;
end get_split_field;

Answer 6

You could use a combination of SUBSTR and INSTR as follows :

Example string : field = 'DE124028#@$1048708#@$000#@$536967136#@$'

The seperator being #@$.

To get the '1048708' for example :

If the field is of fixed length ( 7 here ) :

substr(field,instr(field,'#@$',1,1)+3,7)

If the field is of variable length :

substr(field,instr(field,'#@$',1,1)+3,instr(field,'#@$',1,2) - (instr(field,'#@$',1,1)+3)) 

You should probably look into SUBSTR and INSTR functions for more flexibility.