Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tbl_fiber_invalid_trans_data (SPAN_ID int, 
MAINTENANCE_ZONE_NAME varchar(20),
remarks varchar(10))
INSERT ALL 
    INTO tbl_fiber_invalid_trans_data (SPAN_ID, MAINTENANCE_ZONE_NAME, remarks)
         VALUES (1, 'Zone 1', ',1')
    INTO tbl_fiber_invalid_trans_data (SPAN_ID, MAINTENANCE_ZONE_NAME, remarks)
         VALUES (2, 'Zone 2', ',2')
    INTO tbl_fiber_invalid_trans_data (SPAN_ID, MAINTENANCE_ZONE_NAME, remarks)
         VALUES (3, 'Zone 3', ',1,2')
    INTO tbl_fiber_invalid_trans_data (SPAN_ID, MAINTENANCE_ZONE_NAME, remarks)
         VALUES (4, 'Zone 4', ',2,1')
    INTO tbl_fiber_invalid_trans_data (SPAN_ID, MAINTENANCE_ZONE_NAME, remarks)
         VALUES (5, 'Zone 5', '')
SELECT * FROM dual
;
5 rows affected

create table TBL_INVALID_ERROR_DATA (id int, err_code varchar(30))
INSERT ALL 
    INTO TBL_INVALID_ERROR_DATA (id, err_code)
         VALUES (1, 'Span id length too short')
    INTO TBL_INVALID_ERROR_DATA (id, err_code)
         VALUES (2, 'Inventory suspended')
SELECT * FROM dual
;
2 rows affected

WITH codes AS (
  SELECT DISTINCT SPAN_ID, REGEXP_SUBSTR(remarks, '\d+', 1, level) AS code
  FROM tbl_fiber_invalid_trans_data
  CONNECT BY REGEXP_SUBSTR(remarks, '\d+', 1, level) IS NOT NULL
)
SELECT t1.*, t2.err_code
FROM tbl_fiber_invalid_trans_data t1
JOIN codes c ON c.SPAN_ID = t1.SPAN_ID
LEFT JOIN TBL_INVALID_ERROR_DATA t2 ON t2.id = c.code
ORDER BY t1.SPAN_ID


SPAN_ID
MAINTENANCE_ZONE_NAME
REMARKS
ERR_CODE


1
Zone 1
,1
Span id length too short

2
Zone 2
,2
Inventory suspended

3
Zone 3
,1,2
Inventory suspended

3
Zone 3
,1,2
Span id length too short

4
Zone 4
,2,1
Span id length too short

4
Zone 4
,2,1
Inventory suspended

5
Zone 5
null
null


WITH codes AS (
  SELECT DISTINCT SPAN_ID, REGEXP_SUBSTR(remarks, '\d+', 1, level) AS code
  FROM tbl_fiber_invalid_trans_data
  CONNECT BY REGEXP_SUBSTR(remarks, '\d+', 1, level) IS NOT NULL
),
msgs AS (
  SELECT SPAN_ID, LISTAGG(err_code, ', ') WITHIN GROUP (ORDER BY code) AS err_codes
  FROM codes c
  LEFT JOIN TBL_INVALID_ERROR_DATA t2 ON t2.id = c.code
  GROUP BY SPAN_ID
)
SELECT t1.*, m.err_codes
FROM tbl_fiber_invalid_trans_data t1
JOIN msgs m ON m.SPAN_ID = t1.SPAN_ID
ORDER BY t1.SPAN_ID


SPAN_ID
MAINTENANCE_ZONE_NAME
REMARKS
ERR_CODES


1
Zone 1
,1
Span id length too short

2
Zone 2
,2
Inventory suspended

3
Zone 3
,1,2
Span id length too short, Inventory suspended

4
Zone 4
,2,1
Span id length too short, Inventory suspended

5
Zone 5
null
null

SPAN_ID	MAINTENANCE_ZONE_NAME	REMARKS	ERR_CODE
1	Zone 1	,1	Span id length too short
2	Zone 2	,2	Inventory suspended
3	Zone 3	,1,2	Inventory suspended
3	Zone 3	,1,2	Span id length too short
4	Zone 4	,2,1	Span id length too short
4	Zone 4	,2,1	Inventory suspended
5	Zone 5	null	null