Oracle 18c

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

create table test (data varchar(100))
insert into test values ('HEADER|N1000|E1001|N1002|E1003|N1004|N1005');
1 rows affected

insert into test values ('HEADER|N156|E1|N7|E122|N4|E5');
1 rows affected

insert into test values ('HEADER|E0|E1|E2|E3|E4|E5');
1 rows affected

insert into test values ('HEADER|N0|N1|N2|N3|N4|N5');
1 rows affected

insert into test values ('HEADER|N125');
1 rows affected

select * from test


DATA


HEADER|N1000|E1001|N1002|E1003|N1004|N1005

HEADER|N156|E1|N7|E122|N4|E5

HEADER|E0|E1|E2|E3|E4|E5

HEADER|N0|N1|N2|N3|N4|N5

HEADER|N125


-- all numbers
WITH cte AS (
  SELECT DISTINCT data, level AS l, REGEXP_SUBSTR(data, '[NE]\d+', 1, level) AS num FROM test
  CONNECT BY REGEXP_SUBSTR(data, '[NE]\d+', 1, level) IS NOT NULL
)
SELECT data, LISTAGG(SUBSTR(num, 2), ',') WITHIN GROUP (ORDER BY l) AS "All numbers"
FROM cte
GROUP BY data


DATA
All numbers


HEADER|E0|E1|E2|E3|E4|E5
0,1,2,3,4,5

HEADER|N0|N1|N2|N3|N4|N5
0,1,2,3,4,5

HEADER|N1000|E1001|N1002|E1003|N1004|N1005
1000,1001,1002,1003,1004,1005

HEADER|N125
125

HEADER|N156|E1|N7|E122|N4|E5
156,1,7,122,4,5


-- numbers beginning with E
WITH cte AS (
  SELECT DISTINCT data, level AS l, REGEXP_SUBSTR(data, 'E\d+', 1, level) AS num FROM test
  CONNECT BY REGEXP_SUBSTR(data, 'E\d+', 1, level) IS NOT NULL
)
SELECT data, LISTAGG(SUBSTR(num, 2), ',') WITHIN GROUP (ORDER BY l) AS "E-numbers"
FROM cte
GROUP BY data


DATA
E-numbers


HEADER|E0|E1|E2|E3|E4|E5
0,1,2,3,4,5

HEADER|N0|N1|N2|N3|N4|N5
null

HEADER|N1000|E1001|N1002|E1003|N1004|N1005
1001,1003

HEADER|N125
null

HEADER|N156|E1|N7|E122|N4|E5
1,122,5


-- numbers beginning with N
WITH cte AS (
  SELECT DISTINCT data, level AS l, REGEXP_SUBSTR(data, 'N\d+', 1, level) AS num FROM test
  CONNECT BY REGEXP_SUBSTR(data, 'N\d+', 1, level) IS NOT NULL
)
SELECT data, LISTAGG(SUBSTR(num, 2), ',') WITHIN GROUP (ORDER BY l) AS "N-numbers"
FROM cte
GROUP BY data


DATA
N-numbers


HEADER|E0|E1|E2|E3|E4|E5
null

HEADER|N0|N1|N2|N3|N4|N5
0,1,2,3,4,5

HEADER|N1000|E1001|N1002|E1003|N1004|N1005
1000,1002,1004,1005

HEADER|N125
125

HEADER|N156|E1|N7|E122|N4|E5
156,7,4

DATA	All numbers
HEADER\|E0\|E1\|E2\|E3\|E4\|E5	0,1,2,3,4,5
HEADER\|N0\|N1\|N2\|N3\|N4\|N5	0,1,2,3,4,5
HEADER\|N1000\|E1001\|N1002\|E1003\|N1004\|N1005	1000,1001,1002,1003,1004,1005
HEADER\|N125	125
HEADER\|N156\|E1\|N7\|E122\|N4\|E5	156,1,7,122,4,5

DATA	N-numbers
HEADER\|E0\|E1\|E2\|E3\|E4\|E5	null
HEADER\|N0\|N1\|N2\|N3\|N4\|N5	0,1,2,3,4,5
HEADER\|N1000\|E1001\|N1002\|E1003\|N1004\|N1005	1000,1002,1004,1005
HEADER\|N125	125
HEADER\|N156\|E1\|N7\|E122\|N4\|E5	156,7,4