How to Split a String in Oracle SQL Based on the Furthest Occurrence of a Delimiter?

Splitting a String by Delimiter Position using Oracle SQL

A common task when working with strings is to split them into smaller segments based on a specific delimiter. However, when the delimiter position varies within a string, splitting becomes more complex.

Problem Statement

Suppose you have a string like "F/P/O" and want to split it by the furthest occurrence of the "/" delimiter. The desired result is to separate the string into two parts: "F/P" and "O."

Initial Attempt

The following SQL statement attempts to split the string using the SUBSTR and INSTR functions:

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

However, this query produces an unexpected result, splitting the string into "F" and "/P/O."

Resolution

The issue lies in the use of the INSTR function. By default, INSTR searches for the first occurrence of the delimiter, not the furthest. To find the furthest delimiter, the INSTR function should be modified to search from the end of the string:

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

By starting the search from the end (indicated by the negative start_position -1), the INSTR function locates the last occurrence of the delimiter and splits the string accordingly into "F/P" and "O." This approach ensures that even when there are multiple delimiters in the string, the split occurs at the furthest position.

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