Split String by delimiter position using oracle SQL

Question

I have a string and I would like to split that string by delimiter at a certain position.

For example, my String is F/P/O and the result I am looking for is:

Therefore, I would like to separate the string by the furthest delimiter.
Note: some of my strings are F/O also for which my SQL below works fine and returns desired result.

The SQL I wrote is as follows:

SELECT Substr('F/P/O', 1, Instr('F/P/O', '/') - 1) part1, 
       Substr('F/P/O', Instr('F/P/O', '/') + 1)    part2 
FROM   dual

and the result is:

Why is this happening and how can I fix it?

question from:https://stackoverflow.com/questions/26878291/split-string-by-delimiter-position-using-oracle-sql

Answer 1

Therefore, I would like to separate the string by the furthest delimiter.

I know this is an old question, but this is a simple requirement for which SUBSTR and INSTR would suffice. REGEXP are still slower and CPU intensive operations than the old subtsr and instr functions.

SQL> WITH DATA AS
  2    ( SELECT 'F/P/O' str FROM dual
  3    )
  4  SELECT SUBSTR(str, 1, Instr(str, '/', -1, 1) -1) part1,
  5         SUBSTR(str, Instr(str, '/', -1, 1) +1) part2
  6  FROM DATA
  7  /

PART1 PART2
----- -----
F/P   O

As you said you want the furthest delimiter, it would mean the first delimiter from the reverse.

You approach was fine, but you were missing the start_position in INSTR. If the start_position is negative, the INSTR function counts back start_position number of characters from the end of string and then searches towards the beginning of string.