Use of Oracle instr Functions

INSTR (source string, target string, start position, matching serial number) in Oracle/PLSQL, The instr function returns the position of the string to be truncated in the source string. It is retrieved only once, that is, from the start of the character to the end of the character. Syntax: instr (string1, string2 [, start_position [, nth_appearance]) parameter analysis: string1 source string, which needs to be searched in this string. The string. start_position to be searched in string1 indicates the position of string1. This parameter is optional. If omitted, the default value is 1. The string index starts from 1. If this parameter is positive, it is retrieved from left to right. If this parameter is negative, from right to left, the start index of the string to be searched in the source string is returned. Nth_appearance indicates the number of string2. this parameter is optional. If omitted, the default value is 1. If it is negative, an error is returned. NOTE: If String2 is not found in String1, The instr function returns 0. example: SELECT instr ('syranmo', 's') FROM dual; -- returns 1 SELECT instr ('syranmo', 'A') FROM dual; -- returns 3 1 SELECT instr ('syran Mo', 'A', 1, 2) FROM dual; -- returns 0 (according to the condition, because a appears only once, the fourth parameter 2, that is to say, the location where a appears for 2nd times is obviously not displayed for 2nd times, so 0 is returned. Note that space is a single character !) SELECT instr ('syranmo', 'any',-) FROM dual; -- returns 4 (FROM right to left, the index position should still be determined by the location of the first letter on the left of 'A', so here 4 is returned) SELECT instr ('abc', 'D') FROM dual; -- Return 0 note: you can also use this function to check whether String1 contains String2. If 0 is returned, it means it does not contain; otherwise, it means it contains. As mentioned above, we can use the instr function in this way. Please refer to the following example: If I have a copy of the information, there are some employee ID (field: CODE) above, but now I want to query all their employees, such as names, departments, occupation, etc. Here is an example of two employees with the employee ID 'a10001 'and 'a10002'. If staff is an employee table, the normal practice is as follows: 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 there are many employees, and we think it is troublesome for that, so I thought, can I export it at one time? In this case, you can use the instr function, as shown in the following code: SELECT code, name, dept, occupation FROM staff WHERE instr ('a10001, a10002', code)> 0; the query results are the same, in this way, only two single quotes are used before and after the quotation marks, which is relatively convenient. Another usage is 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%'; this article is FROM the China IT lab