Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT REGEXP_REPLACE(REGEXP_REPLACE('HEADER|N1000|E1001|N1002|E1003|N1004|N1005|', '.*?(\d+)', '\1,'), ',(\|.*)?$', '') FROM dual

REGEXP_REPLACE(REGEXP_REPLACE('HEADER|N1000|E1001|N1002|E1003|N1004|N1005|','.*?(\D+)','\1,'),',(|.*)?$','')

1000,1001,1002,1003,1004,1005

SELECT REGEXP_REPLACE(REGEXP_REPLACE('HEADER|N1000|E1001|N1002|E1003|N1004|N1005|', '.*?N(\d+)', '\1,'), ',(\|.*)?$', '') FROM dual

REGEXP_REPLACE(REGEXP_REPLACE('HEADER|N1000|E1001|N1002|E1003|N1004|N1005|','.*?N(\D+)','\1,'),',(|.*)?$','')

1000,1002,1004,1005

SELECT REGEXP_REPLACE(REGEXP_REPLACE('HEADER|N1000|E1001|N1002|E1003|N1004|N1005|', '.*?E(\d+)', '\1,'), ',(\|.*)?$', '') FROM dual

REGEXP_REPLACE(REGEXP_REPLACE('HEADER|N1000|E1001|N1002|E1003|N1004|N1005|','.*?E(\D+)','\1,'),',(|.*)?$','')

1001,1003

SELECT REGEXP_REPLACE(REGEXP_REPLACE('HEADER|N1000|E1001|N1002|E1003', '.*?E(\d+)', '\1,'), ',(\|.*)?$', '') FROM dual

REGEXP_REPLACE(REGEXP_REPLACE('HEADER|N1000|E1001|N1002|E1003','.*?E(\D+)','\1,'),',(|.*)?$','')

1001,1003

SELECT REGEXP_REPLACE(REGEXP_REPLACE('HEADER|N1000|E1001|N1002|E1003', '.*?N(\d+)', '\1,'), ',(\|.*)?$', '') FROM dual

REGEXP_REPLACE(REGEXP_REPLACE('HEADER|N1000|E1001|N1002|E1003','.*?N(\D+)','\1,'),',(|.*)?$','')

1000,1002

SELECT REGEXP_REPLACE(REGEXP_REPLACE('HEADER|N1000|E1001|N1002|E1003', '.*?(\d+)', '\1,'), ',(\|.*)?$', '') FROM dual

REGEXP_REPLACE(REGEXP_REPLACE('HEADER|N1000|E1001|N1002|E1003','.*?(\D+)','\1,'),',(|.*)?$','')

1000,1001,1002,1003