Oracle_INSTR()

INSTR

admin Functions ,

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.

INSTR( string, substring, {position}, {occurrence} )

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;
STRINGINSTR
admin@dbgeek.in6

Example-2:

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

Example-3:

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

Example-4:

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

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!

You May Also Like..

SUBSTR

Oracle_SUBSTR()

Hey alians, SUBSTR is pretty cool when it comes to extract a substring out of a string in SQL. SUBSTR […]

NULLIF

Oracle SQL_Null Related functions-3_NULLIF

Hi Aliens, Today, we will talk about another Null Related Function, NULLIF. You can read my notes on NVL, NVL2 […]

Translate

Oracle – Translate Function

Hi Geeks, Today, we will talk about an Oracle’s inbuilt function TRANSLATE. TRANSLATE works same as REPLACE function does but […]

Leave a Reply

Your email address will not be published.