Necesito la función Split que tomará dos parámetros, la cadena a dividir y el delimitador para dividir la cadena y devolver una tabla con las columnas Id y Data.Y cómo llamar a la función Split que devolverá una tabla con las columnas Id y Data. La columna Id contendrá la secuencia y la columna Data contendrá los datos de la cadena. Ej.
SELECT*FROM Split('A,B,C,D',',')
El resultado debe tener el siguiente formato:
|Id | Data
-- ----
|1 | A |
|2 | B |
|3 | C |
|4 | D |
A continuación se explica cómo se puede crear una tabla de este tipo:
SELECT LEVEL AS id, REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL) AS data
FROM dual
CONNECT BY REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL) IS NOT NULL;
Con un pequeño ajuste (es decir, sustituyendo el ,
en [^,]
por una variable) se podría escribir una función de este tipo que devolviera una tabla.
Existen múltiples opciones. Véase Dividir una sola cadena delimitada por comas en filas en Oracle
Sólo hay que añadir LEVEL en la lista de selección como columna, para obtener el número de secuencia a cada fila devuelta. O, ROWNUM también sería suficiente.
Usando cualquiera de los siguientes SQLs, podrías incluirlos en una Función.
INSTR en la cláusula CONNECT BY:
SQL> WITH DATA AS 2 ( SELECT 'palabra1, palabra2, palabra3, palabra4, palabra5, palabra6' str FROM dual 3 ) 4 SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str 5 FROM DATA 6 CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0 7 /STR
palabra1 palabra2 palabra3 palabra4 palabra5 palabra6
6 filas seleccionadas.
SQL>
REGEXP_SUBSTR en la cláusula CONNECT BY:
SQL> WITH DATA AS 2 ( SELECT 'palabra1, palabra2, palabra3, palabra4, palabra5, palabra6' str FROM dual 3 ) 4 SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str 5 FROM DATA 6 CONNECT BY regexp_substr(str , '[^,]+', 1, LEVEL) IS NOT NULL 7 /STR
palabra1 palabra2 palabra3 palabra4 palabra5 palabra6
6 filas seleccionadas.
SQL>
REGEXP_COUNT en la cláusula CONNECT BY:
SQL> WITH DATA AS 2 ( SELECT 'palabra1, palabra2, palabra3, palabra4, palabra5, palabra6' str FROM dual 3 ) 4 SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str 5 FROM DATA 6 CONNECT BY LEVEL <= regexp_count(str, ',')+1 7 / STR ---------------------------------------- palabra1 palabra2 palabra3 palabra4 palabra5 palabra66 filas seleccionadas.
SQL>
Usando XMLTABLE
SQL> CON DATOS COMO 2 ( SELECT 'palabra1, palabra2, palabra3, palabra4, palabra5, palabra6' str FROM dual 3 ) 4 SELECT trim(COLUMN_VALUE) str 5 FROM DATA, xmltable(('"' | REPLACE(str, ',', '","') || '"') 6 / STR ------------------------------------------------------------------------ palabra1 palabra2 palabra3 palabra4 palabra5 palabra66 filas seleccionadas.
SQL>
Utilizando la cláusula MODEL:
SQL> WITH t AS 2 ( 3 SELECT 'palabra1, palabra2, palabra3, palabra4, palabra5, palabra6' str 4 FROM dual ) , 5 model_param AS 6 ( 7 SELECT str AS orig_str , 8 ',' 9 || str 10 || ',' AS mod_str , 11 1 AS start_pos , 12 Length(str) AS end_pos , 13 (Length(str) - Length(Replace(str, ',')) + 1 AS element_count , 14 0 COMO element_no , 15 ROWNUM COMO rn 16 FROM t ) 17 SELECT trim(Substr(mod_str, start_pos, end_pos-start_pos)) str 18 FROM ( 19 SELECT * 20 FROM model_param MODEL PARTITION BY (rn, orig_str, mod_str) 21 DIMENSION BY (element_no) 22 MEDIDAS (start_pos, end_pos, element_count) 23 REGLAS ITERAR (2000) 24 UNTIL (ITERATION_NUMBER+1 = element_count[0]) 25 ( start_pos[ITERATION_NUMBER+1] = instr(cv(mod_str), ',', 1, cv(element_no)) + 1, 26 end_pos[iteration_number+1] = instr(cv(mod_str), ',', 1, cv(element_no) + 1) ) ) 27 WHERE element_no != 0 28 ORDER BY mod_str , 29 element_no 30 /STR
palabra1 palabra2 palabra3 palabra4 palabra5 palabra6
6 filas seleccionadas.
SQL>
También puede utilizar el paquete DBMS_UTILITY proporcionado por Oracle. Proporciona varios subprogramas de utilidad. Una de estas utilidades es el procedimiento COMMA_TO_TABLE, que convierte una lista de nombres delimitada por comas en una tabla PL/SQL de nombres.
Si necesitas una función prueba esto.
Primero crearemos un tipo:
CREATE OR REPLACE TYPE T_TABLE IS OBJECT
(
Field1 int
, Field2 VARCHAR(25)
);
CREATE TYPE T_TABLE_COLL IS TABLE OF T_TABLE;
/
Luego crearemos la función:
CREATE OR REPLACE FUNCTION TEST_RETURN_TABLE
RETURN T_TABLE_COLL
IS
l_res_coll T_TABLE_COLL;
l_index number;
BEGIN
l_res_coll := T_TABLE_COLL();
FOR i IN (
WITH TAB AS
(SELECT '1001' ID, 'A,B,C,D,E,F' STR FROM DUAL
UNION
SELECT '1002' ID, 'D,E,F' STR FROM DUAL
UNION
SELECT '1003' ID, 'C,E,G' STR FROM DUAL
)
SELECT id,
SUBSTR(STR, instr(STR, ',', 1, lvl) + 1, instr(STR, ',', 1, lvl + 1) - instr(STR, ',', 1, lvl) - 1) name
FROM
( SELECT ',' || STR || ',' AS STR, id FROM TAB
),
( SELECT level AS lvl FROM dual CONNECT BY level <= 100
)
WHERE lvl <= LENGTH(STR) - LENGTH(REPLACE(STR, ',')) - 1
ORDER BY ID, NAME)
LOOP
IF i.ID = 1001 THEN
l_res_coll.extend;
l_index := l_res_coll.count;
l_res_coll(l_index):= T_TABLE(i.ID, i.name);
END IF;
END LOOP;
RETURN l_res_coll;
END;
/
Ahora podemos seleccionar de ella:
select * from table(TEST_RETURN_TABLE());
Salida:
SQL> select * from table(TEST_RETURN_TABLE());
FIELD1 FIELD2
---------- -------------------------
1001 A
1001 B
1001 C
1001 D
1001 E
1001 F
6 rows selected.
Obviamente, usted tendrá que reemplazar el bit WITH TAB AS...
con el lugar de donde obtendrá los datos reales.
Crédito Crédito