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

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

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

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'));

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