Get subtring from a string in ORACLE

Question

I have this string:

145|567

And i want to extract just the part after the "|" The idea is not using positions because the strings may have different lengths.

Expected result:
567

Thank you very much

Answer 1

Use instr() to get the position of the | and the substr(). For example:

select substr( '123|456', instr( '123|456','|' )+1 ) from dual;

SUB
---
456

Answer 2

Two ways of doing that come to my mind. First one is with regular expression

select regexp_substr('123|456', '|([\d]+)\1') from dual

This way you capture any set of digits following a |

The other one is with substring

select substr('123|456', instr('123|456', '|') + 1) from dual

This way you extraxt the substring starting from the position after the |

Answer 3

If you want to resolve in a Query first you need to know on which position is the character that you need to use like separator, you can use the INSTR function:

SELECT INSTR('145|567', '|') FROM dual;
-- Return 4

With this function you can obtain the position on the string that you use like separator.

Then you can nested this function with SUBSTR that works for extract a portion of a string:

SELECT SUBSTR('145|567', INSTR('145|567', '|') + 1) FROM dual;
-- Return 567

You need to add one position because INSTR return the exactly position of the separator character and if you don't want to return on the SUBSTR action then pass to the next position of the string.

Answer 4

Nothing wrong with parsing by position, as long as your solution handles different length elements. This regexp_substr gets the 2nd element of the list where the elements are followed by a pipe or the end of the line. It doesn't care about lengths of the elements, and handles multiple elements or even NULL elements.

select regexp_substr('123|456', '(.*?)(\||$)', 1, 2, NULL, 1) from dual