SCOTT@orcl12c> SELECT SUBSTR
  2  	      ('AD_VP',
  3  	       INSTR ('AD_VP' || '_', '_', 1, 1) + 1,
  4  	       (INSTR ('AD_VP' || '_', '_', 1, 2)
  5  		- INSTR ('AD_VP' || '_', '_', 1, 1)) + 1)
  6  FROM   DUAL
  7  /

SU
--
VP

1 row selected.

SCOTT@orcl12c> WITH
  2    sample_data AS
  3  	 (SELECT 'AD_VP' AS job_id FROM DUAL UNION ALL
  4  	  SELECT 'AD_PRES' AS job_id FROM DUAL),
  5    lookup_data AS
  6  	 (SELECT 'AD' AS abbrev, 'Administration' AS name FROM DUAL UNION ALL
  7  	  SELECT 'VP' AS abbrev, 'Vice' 	  AS name FROM DUAL)
  8  SELECT job_id,
  9  	    NVL (l1.name, 'NOT-AVAILABLE') AS job_title_1,
 10  	    NVL (l2.name, 'NOT-AVAILABLE') AS job_title_2,
 11  	    NVL (l3.name, 'NOT-AVAILABLE') AS job_title_3
 12  FROM   (SELECT job_id,
 13  		    SUBSTR
 14  		      (job_id,
 15  		       1,
 16  		       INSTR (job_id || '_', '_', 1, 1) - 1) AS job_title_1,
 17  		    SUBSTR
 18  		      (job_id,
 19  		       INSTR (job_id || '_', '_', 1, 1) + 1,
 20  		       (INSTR (job_id || '_', '_', 1, 2)
 21  			- INSTR (job_id || '_', '_', 1, 1)) + 1) AS job_title_2,
 22  		    SUBSTR
 23  		      (job_id,
 24  		       INSTR (job_id || '_', '_', 1, 2) + 1,
 25  		       (INSTR (job_id || '_', '_', 1, 3)
 26  			- INSTR (job_id || '_', '_', 1, 2)) + 1) AS job_title_3
 27  	      FROM   sample_data) s,
 28  	     lookup_data l1,
 29  	     lookup_data l2,
 30  	     lookup_data l3
 31  WHERE  s.job_title_1 = l1.abbrev (+)
 32  AND    s.job_title_2 = l2.abbrev (+)
 33  AND    s.job_title_3 = l3.abbrev (+)
 34  /

JOB_ID  JOB_TITLE_1    JOB_TITLE_2    JOB_TITLE_3
------- -------------- -------------- --------------
AD_PRES Administration NOT-AVAILABLE  NOT-AVAILABLE
AD_VP   Administration Vice           NOT-AVAILABLE

2 rows selected.