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( input_string, start_position [, length ] )
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( input_string, substring[, start_position [, appearance ]] )
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 can be identified as acronym for “sub-string” function. It means to extract a part of the string from the whole||INSTR 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 input||The output datatype of INSTR is number irrespective of the datatype of the input|
|The second argument of SUBSTR function is number||The 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 NULL||If 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 1||In 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 position||If 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.
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 email@example.com. Again I appreciate your visit. Hope to see you again and again!