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?

Isley

possible duplicate of SQL query to translate a list of numbers matched against several ranges, to a list of values – Nancinancie

See also Splitting Delimited Strings - SO Documentation Pages and a discussion on the relative performance is here. – Wiper

Would be nice to know which one of the below is the fastest, possibly a Java function might be the ultimate in speed; depending on whether the APEX ones are C implementations. – Conaway

Answer 1

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;
/

Chariot

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.

Dardan

Thanks for the tip, sadly, I cannot use that package, so I ended up using the functions posted by redFilter. – Isley

The apex function doesn't seem to work with Oracle R12 Applications, since I assume it doesn't contain Apex by default. – Conaway

Sorry, after 4 years I just noticed that I mistyped the package name as "apex_utility" - it is "apex_util" (now corrected). – Dardan

I cannot string_to_table on Oracle 11g, because "Java services" are disabled. However, an SA user could enable them. – Kahn

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;
/

Shockproof

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

Degree

I like this because of the bulk collect into instead of explicitly looping through the regexp result. Since Oracle doesn't provide us with a split() utility, let's at least use the facilities it does provide... – Ambidexterity

Thanks for the solution. How can I get the column name of l_split_table table? To be able to get the data and loop through it. – Harms

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

Priory

The page you mention is very good indeed, but it's better to copy the code here, to make it easier and quicker to check it out. Also better in case of a dead link... – Incorrigible

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.

Art

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

Pretermit

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

Singe

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;

Funnelform

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

Guitar

Answer 11

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

Sequestered

Answer 12

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;

Orthoptic