An explanation of InStr functions in Oracle

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. Retrieved only once, that is, the end of the character from the beginning    to the end of the character.    syntax as follows:   InStr (string1, string2 [, Start_position [, Nth_appearance]])    parametric analysis: &nbsp ;  string1   The source string to find in this string.    string2   the string to find in string1 .   start_position   represents the location of string1 to begin the lookup. 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 string2. This parameter is optional, and if omitted, the default is 1. If the system is negative, it will give an error.    Note:   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;  --returns 3   1 SELECT instr (' Syran Mo ', ' a ', up to) from dual;  --returns 0   (depending on the condition, because a occurs only once, the fourth parameter 2, that is, the 2nd occurrence of the position of a, it is clear that the 2nd time is no longer appearing, so the result returns 0. Note that the space is also counted as a character! )    SELECT InStr (' Syranmo ', ' an ', -1,1)from dual;  --returns 4   (even from right to left, the position of the index is to look at the position of the first letter on the left of ' an ', so this returns 4)    SELECT InStr (' abc ', ' d ') from dual;  --return 0   Note: You can also use this function to check if the String1 contains String2, or if 0 is not included, otherwise it is included.    for the above mentioned, we can use the InStr function in this way. Take a look at the example below:   if I have a profile with some employee's work number (field: CODE), but now I want to check out all of their employee situation, such as name, department, occupation, etc., here is an example of two employees, the work number is ' a10001′, ' a10002′, which assumes that the staff is the employee table, the normal practice is as follows:   1 2 SELECT  code, name, dept, occupation from the staffs  where code In (' A10001 ', ' A10002 ');   or:   SELECT  code, name, dept, occupation from staff  where C Ode = ' A10001 ' OR code = ' A10002 ';   sometimes the staff is more, we for that ' feel more troublesome, so think, can be exported at once? At this point you can use the InStr function, as follows:   SELECT  code, name, dept, occupation from the staff  where InStr (' a10001,a10002 ', Code) >0;   query out the results, so that only two times the single quotation marks, relatively convenient point.    There is another usage, as follows:   SELECT code, name, dept, occupation  from Staff  where InStr (Code, ' 001 ')　> 0;  　Equivalent to    SELECT code, name, dept, occupation  from staff  where code like '%001% ';    &n bsp;        reproduced in it labs

An explanation of InStr functions in Oracle