---

Oracle / PLSQL: Parse a string value and then return a substring

Question: I'm trying to parse a value in an Oracle field. I have to find the occurrence of the 3rd and 4th underscore in a string and then return the value that resides between these two underscores.

For example, the field may contain the following value:

'F:\Siebfile\YD\S_SR_ATT_1-60SS_1-AM3L.SAF'

In this case, I need to return the value of '1-60SS', as this is the value that resides between the 3rd and 4th underscores.

Answer: This is not a simple task. Therefore, we will need to create a function in Oracle that parses the string for us.

The function below uses the INSTR function to determine the positions of the 3rd and 4th underscore. Then it uses the SUBSTR function to return the value between the two underscores.

create or replace function parse_value (pValue varchar2)
   return varchar2
is
   v_pos3 number;
   v_pos4 number;

begin

   /* Return 3rd occurrence of '_' */
   v_pos3 := INSTR (pValue, '_', 1, 3) + 1;

   /* Return 4rd occurrence of '_' */
   v_pos4 := INSTR (pValue, '_', 1, 4);

   return SUBSTR (pValue, v_pos3, v_pos4 - v_pos3);

end parse_value;

Learn more about the INSTR function.

Learn more about the SUBSTR function.