Oracle's InStr function

Transferred from: http://knowyouknowme.iteye.com/blog/554945

1.instr

In Oracle/plsql, theInStr function returns the position of the string to intercept in the source string.

The syntax is as follows:

InStr (string1, string2 [, Start_position [, Nth_appearance]])

string1 The source string to find in this string.

string2 The string to find in the string1 .

start_position represents where string1 is starting to find. This parameter is optional if omitted by default to 1. The string index starts at 1. If this parameter is positive, it is retrieved from left to right, and if this parameter is negative, right-to-left, returns the starting index of the string to find in the source string.

nth_appearance representative to find the first occurrence of the string2. This parameter is optional, and if omitted, the default is 1. If the system is negative, it will give an error.

Attention:

If String2 is not found in String1, the InStr function returns 0.

Apply to:

• Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

To illustrate:

SelectInStr'ABC','a') fromDual --Return1SelectInStr'ABC','BC') fromDual --Return2SelectInStr'ABC abc','a',1,2) fromDual --Return5SelectInStr'ABC','BC',-1,1) fromDual --Return2SelectInStr'ABC','D') fromDual --Return0 

Note: This function can also be used to check if the String1 contains String2, if the return 0 means not included, otherwise the inclusion is represented.

Oracle's InStr function