How Can I Split a String in Oracle SQL at the Furthest Occurrence of a Delimiter?

Splitting Strings by Delimiter Position Using Oracle SQL

Similar to another post, the requirement is to split a string by the furthest occurrence of a delimiter. The key distinction here is that the delimiter is not always the first instance, but the furthest.

To accomplish this, the SUBSTR and INSTR functions can be utilized. Instr(str, '/', -1, 1) locates the position of the furthest delimiter, while -1 indicates to start searching from the end of the string towards the beginning.

However, an error occurred in the provided example due to the absence of the start_position in the INSTR function. By adding start_position = -1, the INSTR function successfully finds the furthest delimiter and splits the string accordingly.

Here is the modified code:

WITH DATA AS
  ( SELECT 'F/P/O' str FROM dual
  )
SELECT SUBSTR(str, 1, Instr(str, '/', -1, 1) -1) part1,
         SUBSTR(str, Instr(str, '/', -1, 1) +1) part2
FROM DATA

This alteration ensures that the code accurately splits the string at the desired delimiter position, resulting in the correct output of "F/P" and "O" for the given string "F/P/O".

The above is the detailed content of How Can I Split a String in Oracle SQL at the Furthest Occurrence of a Delimiter?. For more information, please follow other related articles on the PHP Chinese website!