DEV Community

loading...
Cover image for Using SUBSTR and INSTR functions in ORACLE PLSQL

Using SUBSTR and INSTR functions in ORACLE PLSQL

prsharankumar profile image Sharan Kumar Paratala Rajagopal Updated on ・1 min read

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.

Discussion (0)

pic
Editor guide