add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TYPE string_list AS TABLE OF VARCHAR2(4000);
/
CREATE FUNCTION regexp_split(
value IN VARCHAR2,
regexp_separator IN VARCHAR2 DEFAULT ','
) RETURN string_list PIPELINED DETERMINISTIC
AS
position PLS_INTEGER := 1;
next_position PLS_INTEGER;
BEGIN
IF value IS NULL THEN
RETURN;
END IF;
LOOP
next_position := REGEXP_INSTR( value, regexp_separator, position, 1, 0 );
IF next_position = 0 THEN
PIPE ROW ( SUBSTR( value, position ) );
EXIT;
ELSE
PIPE ROW ( SUBSTR( value, position, next_position - position ) );
position := REGEXP_INSTR( value, regexp_separator, next_position, 1, 1 );
END IF;
END LOOP;
RETURN;
END;
/
CREATE TABLE table_name ( NAME, PROJECT, ERROR ) AS
SELECT 108, 'test1', 'string-1, string-2 ; string-3' FROM DUAL UNION ALL
SELECT 109, 'test2', 'single string' FROM DUAL UNION ALL
SELECT 110, 'test3', 'ab, ,c' FROM DUAL UNION ALL
SELECT 111, 'test4', '1,2,;5,,,9' FROM DUAL UNION ALL
SELECT 112, 'test5', NULL FROM DUAL;
5 rows affected
SELECT t.name,
t.project,
s.COLUMN_VALUE AS error
FROM table_name t
CROSS APPLY TABLE( regexp_split( error, '\s*[,;]\s*' ) ) s
NAME PROJECT ERROR
108 test1 string-1
108 test1 string-2
108 test1 string-3
109 test2 single string
110 test3 ab
110 test3 null
110 test3 c
111 test4 1
111 test4 2
111 test4 null
111 test4 5
111 test4 null
111 test4 null
111 test4 9
SELECT t.name,
t.project,
s.COLUMN_VALUE AS error
FROM table_name t
CROSS JOIN TABLE( regexp_split( t.error, '\s*[,;]\s*' ) ) s
NAME PROJECT ERROR
108 test1 string-1
108 test1 string-2
108 test1 string-3
109 test2 single string
110 test3 ab
110 test3 null
110 test3 c
111 test4 1
111 test4 2
111 test4 null
111 test4 5
111 test4 null
111 test4 null
111 test4 9
SELECT t.name,
t.project,
s.COLUMN_VALUE AS error
FROM table_name t
LEFT OUTER JOIN TABLE( regexp_split( t.error, '\s*[,;]\s*' ) ) s
ON ( 1 = 1 )
NAME PROJECT ERROR
108 test1 string-1
108 test1 string-2
108 test1 string-3
109 test2 single string
110 test3 ab
110 test3 null
110 test3 c
111 test4 1
111 test4 2
111 test4 null
111 test4 5
111 test4 null
111 test4 null
111 test4 9
112 test5 null
SELECT t.name,
t.project,
s.idx,
s.error
FROM table_name t
CROSS APPLY (
SELECT ROWNUM AS idx,
COLUMN_VALUE AS error
FROM TABLE( regexp_split( error, '\s*[,;]\s*' ) )
) s
NAME PROJECT IDX ERROR
108 test1 1 string-1
108 test1 2 string-2
108 test1 3 string-3
109 test2 1 single string
110 test3 1 ab
110 test3 2 null
110 test3 3 c
111 test4 1 1
111 test4 2 2
111 test4 3 null
111 test4 4 5
111 test4 5 null
111 test4 6 null
111 test4 7 9
SELECT t.name,
t.project,
s.idx,
s.error
FROM table_name t
CROSS JOIN
LATERAL (
SELECT ROWNUM AS idx,
COLUMN_VALUE AS error
FROM TABLE( regexp_split( error, '\s*[,;]\s*' ) )
) s
NAME PROJECT IDX ERROR
108 test1 1 string-1
108 test1 2 string-2
108 test1 3 string-3
109 test2 1 single string
110 test3 1 ab
110 test3 2 null
110 test3 3 c
111 test4 1 1
111 test4 2 2
111 test4 3 null
111 test4 4 5
111 test4 5 null
111 test4 6 null
111 test4 7 9
SELECT t.name,
t.project,
s.idx,
s.error
FROM table_name t
LEFT OUTER JOIN
LATERAL (
SELECT ROWNUM AS idx,
COLUMN_VALUE AS error
FROM TABLE( regexp_split( error, '\s*[,;]\s*' ) )
) s
ON ( 1 = 1 )
NAME PROJECT IDX ERROR
108 test1 1 string-1
108 test1 2 string-2
108 test1 3 string-3
109 test2 1 single string
110 test3 1 ab
110 test3 2 null
110 test3 3 c
111 test4 1 1
111 test4 2 2
111 test4 3 null
111 test4 4 5
111 test4 5 null
111 test4 6 null
111 test4 7 9
112 test5 null null