DB GEEK

Hey alians,
While working with the data, INSTR is a very important single row function that returns a numeric value. It searches a substring inside a string and retruns the position of the substring. If you have a worked with Microsoft Excel, it works same as FIND() or SEARCH() but it searches left-to-right and right-to-left with some additional benefits.
Let’s look at the syntax.

position
This is an optional parameter. It defines the position of a character in the string from where Oracle starts searching a substring. It can be a integer value or a column having integer values. It should always be nonzero integer or negative interger. If position is negative, then Oracle counts backward from the end of string and then searches backward from the resulting position.

occurrence
Again, this is an optional parameter. It defines the number of the occurence of the substring in the string to look for. It is a nonzero positive integer and It can be a integer value or a column having integer values.
E.g. if it is 1, Oracle will search the first occurence of substring in the string and if it is 2, Oracle will search the 2nd occurence of substring in the string.

Example-1:

select
'admin@dbgeek.in' STRING,
instr('admin@dbgeek.in','@',1,1) INSTR
from dual;

Example-2:

select
'admin@dbgeek@.in' STRING,
instr('admin@dbgeek@.in','@',1,2) INSTR
from dual;

Example-3:

select
'admin@dbgeek@.in' STRING,
    instr('admin@dbgeek@.in','@',-1,1) instr
from dual;

Example-4:

select
'admin@dbgeek@.in' STRING,
    instr('admin@dbgeek@.in','@',-1,2) instr
from dual;

Hope this blog helped you to understand INSTR function.

If you have any questions/suggestions, please comment below or write me an email.
If you like this blog, do share it with your friends and colleagues on your social media. For more updates join my Facebook group and do like my Facebook page.

Thank you,
Kapil Kumar

Sharing is caring!