Oracle 18c

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
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