Using SUBSTR and INSTR functions in ORACLE PLSQL

For reporting purpose there might be multiple occasions where there will be requirement to select only part of a string before or after a specified delimiter is present. And most challenging part is to get the values as it requires some additional effort to find the part of the string itself.

Here is a simple example to fetch a number field in an address string.
Any value after (# should be fetched as line2 along with the character (# and any value before the (# is line 1.

PLSQL query is as below:

OUTPUT:

Now same example should work and column2 should be null if the matching string is not present in the input string.

OUTPUT:

QUERY:

OUTPUT:

QUERY:

OUTPUT:

CONCLUSION:
You will be now able to get the string before and after a specific character using SUBSTR and INSTR functions in oracle.