Extract the second word from a string in ODI Expression

Question

This two syntaxes allow to get the scond word from a string in oracle

SELECT REGEXP_SUBSTR('Hello this is an example', '\s+(\w+)\s') AS syntax1,
       SUBSTR('Hello this is an example', 
              INSTR('Hello this is an example', ' ', 1, 1) + 1, 
              INSTR('Hello this is an example', ' ', 1, 2) 
              - INSTR('Hello this is an example', ' ', 1)
       ) AS syntax2 
  FROM dual;

Result:

syntax1  syntax2
-------  -------
this     this

---

I'm working in ODI (oracle data integration), this two syntaxes doesn't work in ODI: For ODI, the regexp is not valid and INSTR function accepts only 2 parameters

Can you suggest me a solution that can work in ODI?

Thank you.

Answer 1

I finaly used this expression:

SELECT 
          SUBSTR (
             SUBSTR ('one two three four',
                     INSTR ('one two three four', ' ') + 1,
                     999999),
             0,
               INSTR (
                  SUBSTR ('one two three four',
                          INSTR ('one two three four', ' ') + 1,
                          999999),
                  ' ')
             - 1)
  FROM DUAL