string - por - ¿Hay una función para dividir una cadena en PL/SQL?
separar cadena oracle (10)
A continuación encontrará un ejemplo que puede ser útil
--1ª subcadena
select substr(''alfa#bravo#charlie#delta'', 1,
instr(''alfa#bravo#charlie#delta'', ''#'', 1, 1)-1) from dual;
--2ª subcadena
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ª subcadena
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;
--4ª subcadena
select substr(''alfa#bravo#charlie#delta'', instr(''alfa#bravo#charlie#delta'', ''#'', 1, 3)+1) from dual;
Atentamente
Emanuele
Necesito escribir un procedimiento para normalizar un registro que tenga múltiples tokens concatenados por un char. Necesito obtener estos tokens dividiendo la cadena e insertando cada uno como un nuevo registro en una tabla. ¿Oracle tiene algo así como una función de "división"?
Esto solo funciona en Oracle 10G y superior.
Básicamente, usas regex_substr para hacer una división en la cadena.
Hay apex_util.string_to_table - ver mi respuesta a esta question .
Además, antes de la existencia de la función anterior, una vez publiqué una solución aquí en mi blog .
Actualizar
En versiones posteriores de APEX, apex_util.string_to_table está en deprecated , y se prefiere una función similar apex_string.split .
Hay una manera simple amigos. Use la función REPLACE. Aquí hay un ejemplo de cadena separada por comas lista para pasar a la cláusula IN.
En PL / SQL:
StatusString := REPLACE(''Active,Completed'', '','', '''''','''''');
En SQL Plus:
Select REPLACE(''Active,Completed'', '','', '''''','''''') from dual;
Me gusta el aspecto de esa utilidad apex. Sin embargo, también es bueno saber sobre las funciones estándar de Oracle que puede usar para esto: subStr e inStr http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm
Puede usar regexp_substr (). Ejemplo:
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 consulta itera a través de la cadena separada por comas, busca la coma (,) y luego divide la cadena tratando la coma como delimitador. Devuelve la cadena como una fila, cada vez que golpea un delimitador.
level in statement regexp_substr(''SMITH,ALLEN,WARD,JONES'',''[^,]+'', 1, level) hace referencia a una pseudocolumna en Oracle que se usa en una consulta jerárquica para identificar el nivel de jerarquía en formato numérico: nivel en conectar por
Puede usar una combinación de SUBSTR e INSTR de la siguiente manera:
Ejemplo de cadena: field = ''DE124028#@$1048708#@$000#@$536967136#@$''
El separador es # @ $.
Para obtener el ''1048708'', por ejemplo:
Si el campo es de longitud fija (7 aquí):
substr(field,instr(field,''#@$'',1,1)+3,7)
Si el campo es de longitud variable:
substr(field,instr(field,''#@$'',1,1)+3,instr(field,''#@$'',1,2) - (instr(field,''#@$'',1,1)+3))
Probablemente debería examinar las funciones SUBSTR e INSTR para obtener más flexibilidad.
Si APEX_UTIL no está disponible, tiene una solución usando REGEXP_SUBSTR() .
Inspirado en 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;
/
Tienes que hacer tu propio. P.ej,
/* 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;
/
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;