How to Split a String at the Last Occurrence of a Delimiter in Oracle SQL?

Splitting Strings at a Specific Delimiter Position Using Oracle SQL

Splitting a string at a particular delimiter position is a common requirement in data manipulation tasks. The INSTR and SUBSTR functions in Oracle SQL provide a straightforward approach for achieving this.

In your case, you wanted to split the string "F/P/O" at the furthest delimiter, which in this case is the last forward slash (/). Your original SQL statement, while mostly correct, was missing the critical start_position argument in the INSTR function.

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

By specifying a negative start_position in INSTR, we can count back from the end of the string. In your case, using -1 would account for the last occurrence of the delimiter.

SELECT SUBSTR(str, 1, Instr(str, '/', -1, 1) -1) part1,
       SUBSTR(str, Instr(str, '/', -1, 1) +1) part2
FROM DATA

This modified query will correctly split the string "F/P/O" at the last forward slash, resulting in:

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

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