Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE sample_data (value) AS
  SELECT 'R985AD5768N2' FROM DUAL;
1 rows affected

WITH bounds (value, sspos, sepos, espos, eepos) AS (
  SELECT value,
         1,
         2,
         REGEXP_INSTR(value, 'R|G|N|D5', 2, 1, 0),
         REGEXP_INSTR(value, 'R|G|N|D5', 2, 1, 1)
  FROM   sample_data
  WHERE  value LIKE 'R%'
UNION ALL
  SELECT value,
         espos,
         eepos,
         REGEXP_INSTR(value, 'R|G|N|D5', eepos, 1, 0),
         REGEXP_INSTR(value, 'R|G|N|D5', eepos, 1, 1)
  FROM   bounds
  WHERE  eepos > 0
)
SELECT SUBSTR(value, sspos, sepos - sspos) AS prefix,
       CASE WHEN espos = 0
       THEN SUBSTR(value, sepos)
       ELSE SUBSTR(value, sepos, espos - sepos)
       END AS suffix
FROM   bounds


PREFIX
SUFFIX


R
985A

D5
768

N
2


SELECT value,
       REGEXP_SUBSTR(value, '^R(.*?)(R|G|N|D5|$)', 1, 1, NULL, 1) AS r,
       REGEXP_SUBSTR(value, 'G(.*?)(R|G|N|D5|$)', 1, 1, NULL, 1) AS g,
       REGEXP_SUBSTR(value, 'N(.*?)(R|G|N|D5|$)', 1, 1, NULL, 1) AS n,
       REGEXP_SUBSTR(value, 'D5(.*?)(R|G|N|D5|$)', 1, 1, NULL, 1) AS d5
FROM   sample_data


VALUE
R
G
N
D5


R985AD5768N2
985A
null
2
768


DECLARE
  value VARCHAR2(20) := 'R985AD5768N2';
  r  VARCHAR2(20) := REGEXP_SUBSTR(value, '^R(.*?)(R|G|N|D5|$)', 1, 1, NULL, 1);
  g  VARCHAR2(20) := REGEXP_SUBSTR(value, 'G(.*?)(R|G|N|D5|$)', 1, 1, NULL, 1);
  n  VARCHAR2(20) := REGEXP_SUBSTR(value, 'N(.*?)(R|G|N|D5|$)', 1, 1, NULL, 1);
  d5 VARCHAR2(20) := REGEXP_SUBSTR(value, 'D5(.*?)(R|G|N|D5|$)', 1, 1, NULL, 1);
BEGIN
  DBMS_OUTPUT.PUT_LINE( 'R = ' || r);
  DBMS_OUTPUT.PUT_LINE( 'G = ' || g);
  DBMS_OUTPUT.PUT_LINE( 'N = ' || n);
  DBMS_OUTPUT.PUT_LINE( 'D5 = ' || d5);
END;
/
1 rows affected

dbms_output:
R = 985A
G = 
N = 2
D5 = 768