Is there a function to split a string in Oracle 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

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 2

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 3

You have to roll your own. E.g.,

/* from :http://www.builderau.com.au/architect/database/soa/Create-functions-to-join-and-split-strings-in-Oracle/0,339024547,339129882,00.htm

select split('foo,bar,zoo') from dual;
select * from table(split('foo,bar,zoo'));

pipelined function is SQL only (no PL/SQL !)
*/

create or replace type split_tbl as table of varchar2(32767);
/
show errors

create or replace function split
(
    p_list varchar2,
    p_del varchar2 := ','
) return split_tbl pipelined
is
    l_idx    pls_integer;
    l_list    varchar2(32767) := p_list;
    l_value    varchar2(32767);
begin
    loop
        l_idx := instr(l_list,p_del);
        if l_idx > 0 then
            pipe row(substr(l_list,1,l_idx-1));
            l_list := substr(l_list,l_idx+length(p_del));

        else
            pipe row(l_list);
            exit;
        end if;
    end loop;
    return;
end split;
/
show errors;

/* An own implementation. */

create or replace function split2(
  list in varchar2,
  delimiter in varchar2 default ','
) return split_tbl as
  splitted split_tbl := split_tbl();
  i pls_integer := 0;
  list_ varchar2(32767) := list;
begin
  loop
    i := instr(list_, delimiter);
    if i > 0 then
      splitted.extend(1);
      splitted(splitted.last) := substr(list_, 1, i - 1);
      list_ := substr(list_, i + length(delimiter));
    else
      splitted.extend(1);
      splitted(splitted.last) := list_;
      return splitted;
    end if;
  end loop;
end;
/
show errors

declare
  got split_tbl;

  procedure print(tbl in split_tbl) as
  begin
    for i in tbl.first .. tbl.last loop
      dbms_output.put_line(i || ' = ' || tbl(i));
    end loop;
  end;

begin
  got := split2('foo,bar,zoo');
  print(got);
  print(split2('1 2 3 4 5', ' '));
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

This only works in Oracle 10G and greater.

Basically, you use regex_substr to do a split on the string.

https://blogs.oracle.com/aramamoo/entry/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement

---

Edit:

Archived link: http://web.archive.org/web/20170304121704/https://blogs.oracle.com/aramamoo/entry/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement

The code:

select * from emp where ename in (
  select regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) from dual
  connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null 
);

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.

Answer 7

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 8

In Oracle, below SQL will split myString to substring:

WITH rws AS (
    SELECT
        'str1,STR2,stR3,StR4' myString
    FROM
        dual
) SELECT
    regexp_substr(
        myString,
        '[^,]+',
        1,
        level
    ) value
  FROM
    rws
CONNECT BY
    level <= length(myString) - length(
        replace(
            myString,
            ','
        )
    ) + 1;

Result is:

str1
STR2
stR3
StR4

Answer 9

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 10

I needed a function that splits a clob and makes sure the function is usable in sql.

create or replace type vchar_tab is table of varchar2(4000)
/

create or replace function split(
    p_list in clob,
    p_separator in varchar2 default '|'
) return vchar_tab pipelined is
    C_SQL_VCHAR_MAX constant integer:=4000;
    C_MAX_AMOUNT    constant integer:=28000;
    C_SEPARATOR_LEN constant integer:=length(p_separator);
    l_amount        integer:=C_MAX_AMOUNT;
    l_offset        integer:=1;
    l_buffer        varchar2(C_MAX_AMOUNT);
    l_list          varchar2(32767);
    l_index         integer;
begin
    if p_list is not null then
        loop
            l_index:=instr(l_list, p_separator);
            if l_index > C_SQL_VCHAR_MAX+1 then
                raise_application_error(-20000, 'item is too large for sql varchar2: len='||(l_index-1));
            elsif l_index > 0 then -- found an item, pipe it
                pipe row (substr(l_list, 1, l_index-1));
                l_list:=substr(l_list, l_index+C_SEPARATOR_LEN);
            elsif length(l_list) > C_SQL_VCHAR_MAX then
                raise_application_error(-20001, 'item is too large for sql varchar2: length exceeds '||length(l_list));
            elsif l_amount = C_MAX_AMOUNT then -- more to read from the clob
                dbms_lob.read(p_list, l_amount, l_offset, l_buffer);
                l_list:=l_list||l_buffer;
            else -- read through the whole clob
                if length(l_list) > 0 then
                    pipe row (l_list);
                end if;
                exit;
            end if;
        end loop;
    end if;

    return;
exception
    when no_data_needed then -- this happens when you don't fetch all records
        null;
end;
/

Test:

select *
from table(split('ASDF|IUYT|KJHG|ASYD'));

Answer 11

There is a simple way folks. Use REPLACE function. Here is an example of comma separated string ready to be passed to IN clause.

In PL/SQL:

StatusString :=   REPLACE('Active,Completed', ',', ''',''');

In SQL Plus:

Select  REPLACE('Active,Completed', ',', ''',''') from dual;

Answer 12

I like the look of that apex utility. However its also good to know about the standard oracle functions you can use for this: subStr and inStr http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm