Difference Between SUBSTR and INSTR With An Easy Trick to Remember Them

SUBSTR and INSTR are the conventional functions of Oracle PL/SQL. The usage of these functions is very clear but sometimes amidst the pile of several syntaxes, programmer tends to forget while most of the times go confused. The difference between SUBSTR and INSTR is also interviewers’ favorite question. There is a very simple method to understand the difference between the two in the blink of an eye. In this article, in addition to understanding the utilization of SUBSTR and INSTR, we will go through their difference and a simple trick to remember them in an easy way.

SUBSTR Syntax

SUBSTR( input_string, start_position [, length ] )

Examples

SELECT SUBSTR('OracleMine',2,5) FROM DUAL;

Output
------
racle (Select 5 characters starting from the position 2)

SELECT SUBSTR('OracleMine',7) FROM DUAL;

Output
------
Mine (Select all characters starting from the position 7)

INSTR Syntax

INSTR( input_string, substring[, start_position [, appearance ]] )

Examples

SELECT INSTR('OracleMine','e', 1,2) FROM DUAL;

Output
------
10 (Starting from position 1 give the place of the second occurrence of letter 'e')

SELECT INSTR('OracleMine','e',3) FROM DUAL;

Output
------
6 (Starting from position 3 give the place of the first occurrence of letter 'e'. 
As there is no mention of appearance by default it is taken as 1)

Difference between SUBSTR and INSTR

SUBSTR

INSTR

SUBSTR can be identified as acronym for “sub-string” function. It means to extract a part of the string from the wholeINSTR can be identified as “in-string” function. The meaning of INSTR function can be derived as the occurrence of a substring in the string
The output datatype of SUBSTR is number for numeric input and character for date and character inputThe output datatype of INSTR is number irrespective of the datatype of the input
 The second argument of SUBSTR function is numberThe second argument of INSTR function can be a number for the number input and character for the date and string inputs
If the start position of SUBSTR is greater than the total length of the input, the output will be returned as NULLIf the start position of the INSTR function is greater than the total length of the input, the output will be returned as 0
In SUBSTR function if the start position is passed as 0 (zero) then by default the start position is taken as 1In INSTR function if the start position is passed as 0 (zero)  then output is returned as 0 (zero)
If the “length” argument is not passed, then the output will be the whole input from the start positionIf the “appearance” argument is not passed, then by default value 1 is considered for the appearance of the string pattern

Simple Trick to remember SUBSTR and INSTR

Seasoned or expert programmers may not find this trick helpful but for the novice programmers or beginners this trick can be a very easy way to remember the difference between SUBSTR and INSTR functions.

SUBSTR function starts with letter ‘S’ which is the first letter of word “String”. So the output of SUBSTR function can be a number or a string.

INSTR function starts with letter ‘I’ which resembles numeric digit 1 (one). So the output of INSTR function can be only a number (in this case position).

Hope you like this article and find it useful. We highly appreciate comments and feedback.

You can also Subscribe here to stay updated on latest posts of Oraclemine.com.

Hi I am Paras.

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.