Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

People also ask

Is there a split function in Oracle?

Need Split function which will take two parameters, string to split and delimiter to split the string and return a table with columns Id and Data. And how to call Split function which will return a table with columns Id and Data. Id column will contain sequence and data column will contain data of the string.

Can Substr be used in PL SQL?

The PLSQL SUBSTR function is used for extracting a substring from a string. The SUBSTR function accepts three parameters which are input_string, start_position, length. SUBSTR calculates lengths using characters as defined by the input character set. SUBSTRB uses bytes instead of characters.

How Split comma separated string and pass to in clause of select statement?

You need to combine two nested statement: Select * From Employee Emp Where Emp. Emp_Name In (Select Regexp_Substr('KING,JONES,FORD' ,'[^,]+' ,1 ,Level) Emp_Name From Dual Connect By Regexp_Substr('KING,JONES,FORD' ,'[^,]+' ,1 ,Level) Is Not Null);


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.


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

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

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


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