DEV Community

Cover image for Using SUBSTR and INSTR functions in ORACLE PLSQL
Sharan Kumar Paratala Rajagopal
Sharan Kumar Paratala Rajagopal

Posted on • Updated on

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:

Alt Text

OUTPUT:

Alt Text

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

Alt Text

OUTPUT:

Alt Text

QUERY:

Alt Text

OUTPUT:

Alt Text

QUERY:

Alt Text

OUTPUT:

Alt Text

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

Top comments (0)