Oracle instr function

In Oracle / PLSQL in, instr function returns the string you want to intercept the location in the source string. Retrieve only once, that is, from the beginning of the character

To the end of the character on the end.

Syntax is as follows:

instr (string1, string2 [, start_position [, nth_appearance]])

Parametric analysis:

string1

Source string, this string to find.

string2

To search string in string1.

start_position

Representative of where to start looking string1. This parameter is optional, if omitted, the default is 1. String index starts from 1. If this parameter is positive, from left to right beginning retrieval, if this parameter is negative, right-to-left search, return to the search string in the source string starting index.

nth_appearance

To find the first several appearances on behalf of string2. This parameter is optional, if omitted, the default is 1 if it is negative the system error.

Note:

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

Example:

SELECT instr ('syranmo', 's') FROM dual; - return a

SELECT instr ('syranmo', 'ra') FROM dual; - returns 3

1 SELECT instr ('syran mo', 'a', 1,2) FROM dual; - return 0

(Depending on conditions, because a appears only once, the fourth parameter 2, that appeared a 2nd location, apparently the 2nd is no longer there, so the result returns 0. Note the space can be considered a character!)

SELECT instr ('syranmo', 'an', -1,1) FROM dual; - Back 4

(Even number from right to left, the index will depend on the position of 'an' the first letter of the left position, so here to return 4)

SELECT instr ('abc', 'd') FROM dual; - return 0

Note: You can also use this function to check if it contains String1 String2, returns 0 if not included, or that contain.

For the above mentioned, we can use instr function. Consider the following example:

If I have an information, the above are some of the employee's job number (Field: CODE), but now I want to check out all of their employees, for example name, department, occupation, etc., there are two employees for example, ID namely, 'A10001', 'A10002', where staff are employees table assuming that the normal practice on the following:

1 2 SELECT code, name, dept, occupation FROM staff WHERE code IN ('A10001', 'A10002');

Or:

SELECT code, name, dept, occupation FROM staff WHERE code = 'A10001' OR code = 'A10002';

Sometimes employees are more, that we 'think is too much trouble, so wanted to, you can turn it off it? At this time you can use instr function, as follows:

SELECT code, name, dept, occupation FROM staff WHERE instr ('A10001, A10002', code)> 0;

Check out the same results, so only use two single quotation marks around the relatively convenient point.

There is also a usage, as follows:

SELECT code, name, dept, occupation FROM staff WHERE instr (code, '001 ')> 0;

Equivalent

SELECT code, name, dept, occupation FROM staff WHERE code LIKE '% 001%';