Oracle InStr functions in a detailed

INSTR

(Source string, target string, start position, match ordinal)

In Oracle/plsql, the InStr function returns the position of the string to intercept in the source string. Retrieve only once, that is, from the beginning of the character

Ends at the end of the character.

The syntax is as follows:

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

Parametric analysis:

String1

The source string to look up in this string.

string2

The string to find in the string1.

Start_position

Represents the location of the string1 to begin the search. 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

Represents the string2 to find the occurrence of the first few occurrences. 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.

Example:

SELECT InStr (' Syranmo ', ' s ') from dual; --Return 1

SELECT InStr (' Syranmo ', ' RA ') from dual; --Return 3

1 SELECT InStr (' Syran Mo ', ' a ', up to) from dual; --Return 0

(depending on the condition, because a only occurs once, the fourth parameter 2, that is, the 2nd occurrence of a position, it is clear that the 2nd time is no longer appear, so the result returns 0.) Note that the space is also counted as a character! ）

SELECT InStr (' Syranmo ', ' an ', -1,1) from dual; --Return 4

(even from right to left, the position of the index is to look at the position of the first letter on the left side of ' an ', so this returns 4)

SELECT InStr (' abc ', ' d ') from dual; --Return 0

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.

For the above mentioned, we can use the InStr function in this way. Take a look at the following example:

If I have a profile with some employee's work number (field: CODE), but now I have to check out all of their employees, such as name, department, occupation and so on, here is an example of two employees, the work number is ' a10001′, ' a10002′, which assumes the staff table, The normal practice is as follows:

1 2 SELECT code, name, dept, occupation from the staff WHERE code in (' A10001 ', ' A10002 ');

Or:

SELECT code, Name, dept, occupation from staff WHERE code = ' A10001 ' OR code = ' A10002 ';

Sometimes the staff is more, we for that ' feel more troublesome, so think, can be exported at once? You can then use the InStr function, as follows:

SELECT code, Name, dept, occupation from staff WHERE InStr (' a10001,a10002 ', code) >0;

Query out the same results, so that only two times the single quotation marks, relatively convenient point.

There is one more usage, as follows:

SELECT code, Name, dept, occupation from the staff WHERE InStr (Code, ' 001 ') > 0;

Equivalent to

SELECT code, Name, dept, occupation from the staff WHERE code like '%001% ';

Oracle InStr functions in a detailed