INSTR ("in-string") is a function used to find sub-string in strings.
The general syntax of INSTR is:
INSTR (string to search, search pattern [, start [, occurrence]])
The arguments within brackets [] are optional.
INSTR returns a location within the string where search pattern begins.
Here are some examples of the use of the INSTR function:
SQL> SQL> SELECT INSTR('This is a test','is') 2 FROM dual;-- from w w w. ja v a2 s. c o m INSTR('THISISATEST','IS') ------------------------- 00003.00 SQL>
The first character of string to search is numbered 1.
Since "is" is the search pattern, it is found in string to search at position 3.
If we had chosen to look for the second occurrence of "is," the query would look like this:
SQL> SQL> SELECT INSTR('This is a test','is',1,2) 2 FROM dual;-- www . j a v a 2 s. c o m INSTR('THISISATEST','IS',1,2) ----------------------------- 00006.00 SQL>
In this case, the second occurrence of "is" is found at position 6 of the string.
To find the second occurrence, we have to tell the function where to start.
Therefore the third argument starts the search in position 1 of string to search.
If a fourth argument is desired, then the third argument is mandatory.
If search pattern is not in the string, the INSTR function returns 0, as shown by the query below:
SQL> SQL> SELECT INSTR('This is a test','abc',1,2) 2 FROM dual;-- from ww w . j a v a 2s . c o m INSTR('THISISATEST','ABC',1,2) ------------------------------ 00000.00 SQL>