Extract Before Symbol in Oracle: SUBSTR & INSTR

In Oracle, you can use the SUBSTR and INSTR functions to extract content before a specific symbol. Here is an example:

If we have a string “abc:def:ghi”, we want to extract the content before the first “:” symbol which is “abc”.

SELECT SUBSTR('abc:def:ghi', 1, INSTR('abc:def:ghi', ':') - 1) FROM dual;

The INSTR function is used in the above query to locate the position of the first “:” and then the SUBSTR function is used to extract a substring based on this position.

To extract the content “abc:def” before the second “:” symbol, you can do the following:

SELECT SUBSTR('abc:def:ghi', 1, INSTR('abc:def:ghi', ':', 1, 2) - 1) FROM dual;

Similarly, the third parameter of the INSTR function indicates the position from which to start searching for the character.