Existe uma function para dividir uma string em PL / SQL?

Eu preciso escrever um procedimento para normalizar um registro que tenha vários tokens concatenados por um caractere. Eu preciso obter esses tokens dividindo a string e inserir cada um como um novo registro em uma tabela. O Oracle tem algo parecido com uma function “split“?

    Você tem que rolar o seu próprio. Por exemplo,

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

    apex_util.string_to_table – veja minha resposta a essa pergunta .

    Além disso, antes da existência da function acima, uma vez eu postei uma solução aqui no meu blog .

    Atualizar

    Em versões posteriores do APEX, apex_util.string_to_table é preterido e uma function semelhante apex_string.split é preferida.

    Se APEX_UTIL não estiver disponível, você terá uma solução usando REGEXP_SUBSTR() .

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

    Isso só funciona no Oracle 10G e superior.

    Basicamente, você usa regex_substr para fazer um split na string.

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

    Você poderia usar uma combinação de SUBSTR e INSTR da seguinte maneira:

    Exemplo: string field = 'DE124028#@$1048708#@$000#@$536967136#@$'

    O separador sendo # @ $.

    Para obter o ‘1048708’ por exemplo:

    Se o campo é de comprimento fixo (7 aqui):

     substr(field,instr(field,'#@$',1,1)+3,7) 

    Se o campo é de tamanho variável:

     substr(field,instr(field,'#@$',1,1)+3,instr(field,'#@$',1,2) - (instr(field,'#@$',1,1)+3)) 

    Você provavelmente deve procurar nas funções SUBSTR e INSTR para obter mais flexibilidade.

    Você pode usar regexp_substr (). Exemplo:

     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; 

    A consulta percorre a sequência separada por vírgulas, procura a vírgula (,) e, em seguida, divide a cadeia tratando a vírgula como delimitador. Retorna a string como uma linha sempre que atinge um delimitador.

    level na instrução regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) refere-se a uma pseudocoluna no Oracle que é usada em uma consulta hierárquica para identificar o nível de hierarquia no formato numérico: level em conectar por

    Por favor, encontre um exemplo que você pode achar útil

    – 1ª substring

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

    – 2ª 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; 

    – 3ª 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; 

    – substring

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

    Cumprimentos

    Emanuele

     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; 

    Eu gosto da aparência desse utilitário de apex. No entanto, também é bom saber sobre as funções oracle padrão que você pode usar para isso: subStr e inStr http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm

    Existe uma maneira simples de pessoas. Use a function REPLACE. Aqui está um exemplo de uma string separada por vírgula pronta para ser passada para a cláusula IN.

    Na PL / SQL:

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

    No SQL Plus:

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