Y a-t-il une fonction pour séparer une chaîne en PL/SQL?
j'ai besoin d'écrire une procédure pour normaliser un enregistrement qui ont plusieurs jetons concaténés par un char. J'ai besoin d'obtenir ces jetons séparant la chaîne et insérant chacun comme un nouvel enregistrement dans une table. Est-ce que Oracle a quelque chose comme une fonction "split"?
10 réponses
vous devez rouler le vôtre. Par exemple,
/* 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;
/
Il y a apex_util.string_to_table - voir ma réponse à cette question .
aussi, avant l'existence de la fonction ci-dessus, j'ai une fois posté une solution ici sur mon blog .
mise à Jour
dans les versions ultérieures D'APEX, apex_util.string_to_table est déprécié , et une fonction similaire apex_string.split est préféré.
si APEX_UTIL n'est pas disponible, vous avez une solution en utilisant REGEXP_SUBSTR() .
inspiré de 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;
/
cela ne fonctionne que dans Oracle 10G et plus.
en gros, vous utilisez regex_substr pour faire une Division sur la chaîne.
vous pouvez utiliser une combinaison de SUBSTR et INSTR comme suit:
exemple de chaîne: field = 'DE124028#@48708#@"151920920"0#@6967136#@$'
L'élément de séparation en cours de #@$.
pour obtenir le '1048708' par exemple:
si le champ est de longueur fixe (7 ici ):
substr(field,instr(field,'#@$',1,1)+3,7)
si le champ est de longueur variable:
substr(field,instr(field,'#@$',1,1)+3,instr(field,'#@$',1,2) - (instr(field,'#@$',1,1)+3))
vous devriez probablement regarder dans les fonctions SUBSTR et INSTR pour plus de flexibilité.
Veuillez trouver en suivant un exemple que vous pouvez trouver utile
--1er substrat
select substr('alfa#bravo#charlie#delta', 1,
instr('alfa#bravo#charlie#delta', '#', 1, 1)-1) from dual;
--2e substrat
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;
--3e sous-couche
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;
--4e substrat
select substr('alfa#bravo#charlie#delta', instr('alfa#bravo#charlie#delta', '#', 1, 3)+1) from dual;
meilleures salutations
Emmanuel
vous pouvez utiliser regexp_substr(). Exemple:
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;
la requête itérate à travers la chaîne de caractères séparée par une virgule, recherche la virgule (,) puis divise la chaîne de caractères en traitant la virgule comme délimiteur. Il renvoie la chaîne comme une rangée, chaque fois qu'elle touche un délimiteur.
level dans la déclaration regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) se réfère à une pseudocolumn dans Oracle qui est utilisé dans une requête hiérarchique pour identifier le niveau de la hiérarchie dans le format numérique: niveau de connecter par
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;
j'aime l'apparence de cet utilitaire apex. Cependant, il est également bon de connaître les fonctions oracle standard que vous pouvez utiliser pour cela: subStr et inStr http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm
il y a un moyen simple les gens. Utilisez la fonction Remplacer. Voici un exemple de chaîne séparée par des virgules prête à passer dans la clause.
in PL/ SQL:
StatusString := REPLACE('Active,Completed', ',', ''',''');
en SQL Plus:
Select REPLACE('Active,Completed', ',', ''',''') from dual;