The Oracle INSTR
SQL function is popular and performs materially the same operation as instr
functions in many other programming languages.
Below shows the INSTR
function along with the arguments it takes:
1 | instr(string, SUBSTRING) |
1 | instr(string, SUBSTRING, start_position) |
1 | instr(string, SUBSTRING, start_position, occurrence) |
The Oracle INSTR
function returns the position (an integer) within string
of the first character in substring
that was found while using the corresponding start_position
and occurrence
.
Following are important rules to follow along with syntax exemplifying the implications of the rules.
- The first character of
string
is atstart_position
1.start_position
is defaulted as 1. Ifstart_position
is set to 0, 0 will always be returned, and thus, is not a useful value. Ifstart_position
is negative, searching forsubstring
will begin atstart_position
characters counted from the end (right) ofstring
and searching will be conducted towards the start (right-to-left) ofstring
.1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
SELECT instr('abcdabcd','b') FROM dual --returns: 2 SELECT instr('abcdabcd','b',1) FROM dual --returns: 2 SELECT instr('abcdabcd','b',4) FROM dual --returns: 6 SELECT instr('abcdabcd','b',0) FROM dual --returns: 0 SELECT instr('abcdabcd','b',-1) FROM dual --returns: 6 SELECT instr('abcdabcd','b',-4) FROM dual --returns: 2
substring
may exist withinstring
more than once. Theocurrence
attribute is a positive integer that allows you specify which occurrence you are searching for. Remember, if you would like to find the 2cnd-to-last occurrence ofsubstring
withinstring
, you should search using astart_position
of -1 to indicate that searching should be conducted backwards, leavingoccurrence
at a value of 2.1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
SELECT instr('abcdabcd','b',1,1) FROM dual --returns: 2 SELECT instr('abcdabcd','b',1,2) FROM dual --returns: 6 SELECT instr('abcdabcd','b',1,0) FROM dual --returns: ORA-01428: argument '0' is out of range SELECT instr('abcdabcd','b',1,-1) FROM dual --returns: ORA-01428: argument '-1' is out of range SELECT instr('abcdabcd','b',-1,1) FROM dual --returns: 6 SELECT instr('abcdabcd','b',-1,2) FROM dual --returns: 2
string
and substring
can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. Both start_position
and ocurrence
must be of datatype NUMBER, or any datatype that can be implicitly converted to NUMBER, and must resolve to an integer (remember that start_position
should not be 0 and ocurrence
should be greater than or equal to 1). The return value is a NUMBER, and will return 0 when nothing is found in the search.
INSTR
is most powerful and often used in practice with the Oracle SUBSTR
SQL function. Here are some examples of how to combine SUBSTR with INSTR.
8 Comments on “Oracle INSTR Function – SQL Syntax Examples”
Thanks for above examples. It really help me lot.
You’re welcome, Mukesh. Glad this helped.
Hello Team,
I need some more explaination on how the below syntax is working
“SELECT instr(‘abcdabcd’,’b’,1,1) FROM dual”
Thanks & regards,
Souvik Ghosh
this syntax finds the first occurrence of “b” starting front he first character on the left, hence it will return the value 2. The syntax instr(‘abcdabcd’,’b’,1,2) finds the second occurrence and would return the value 6. you can also start from the right and search left. so instr(‘abcdabcd’,’a’,-1,1) would return 5, instr(‘abcdabcd’,’a’,-1,2) would return 1.
Thanks for this. Got me headed in the right direction. I needed to parse out the second to last level in a URL where the “root” could vary (parsing Sharepoint folders). So, here is what I came up with:
substr(doc_url,instr(document_url,’/’,-1,2)+1,instr(doc_url,’/’,-1,1)-instr(doc_url,’/’,-1,2)-1)
returns hello for
http://a.com/b/hello/world.doc and for
http://a.com/hello/world.doc
hi!
i need help on nvarchar2, i tried storing values in different languages using nvarchar2;but once i retrieved them, they were in unspecified form….please help me out here…..
thank you!
SELECT instr(‘abcdabcd’,’b’,-1,1)
I did not understand how the result is 6 of this code.Please can anyone tell me about this?
The negative one (-1) indicates that the search will start on the right to left and find the first “b”. That position of “b” happens to be sixth from the left of the overall string. Hope this helps!