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

Question

I need to write a procedure to normalize a record with multiple tokens combined by one char. I need to get these tokens separating a row and paste them into a new record in the table. Does Oracle have something like a split function?

Answer 1

You need to collapse yourself. For example.

/* 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 2

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

Also, before the existence of the aforementioned function, I once posted a solution here on my blog .

Update

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

Answer 3

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

Inspired by 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

This only works on Oracle 10G and above.

Basically, you use regex_substr to split into a string.

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

Answer 5

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

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

Separator # @ $.

To get "1048708", for example:

If the field has a fixed length (here: 7):

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

For more flexibility, you should probably look into the SUBSTR and INSTR functions.

Answer 6

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 a string separated by commas, searches for a comma (,), and then breaks the string, treating the comma as a separator. It returns a string as a string, whenever it hits the delimiter.

level in the regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) expression regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) refers to the pseudo-column in Oracle, which is used in a hierarchical query to identify the hierarchy level in numeric format: level in the connection

Answer 7

In the following example you can find useful

- 1st substring

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

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

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

Regards

Emanuele

Answer 8

 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 9

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

Answer 10

There are ordinary people. Use the REPLACE function. The following is an example of a comma separated string ready to be passed to the IN clause.

In PL / SQL:

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

In SQL Plus:

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