The Oracle InStr function uses

INSTR (source string, target string, starting position, matching ordinal):
In Oracle/plsql, the InStr function returns the position of the string to intercept in the source string. It is retrieved only once, that is, from the beginning of the character to the end of the character.

first, the syntax is as follows:
InStr (string1, string2 [, Start_position [, Nth_appearance]])
1> parameter Analysis:
①string1: The source string to look up in this string.

②string2: The string to find in string1.
③start_position: Represents the location of the string1 to begin the search.

Note: This parameter is optional, if omitted, the default is 1. The string index starts at 1.

If this parameter is positive, the search starts from left to right.

If this parameter is negative, it is retrieved from right to left, returning 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.

Note: 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.
2> Example:
SELECT InStr (' Syranmo ', ' s ') from dual; --Return 1
SELECT InStr (' Syranmo ', ' RA ') from dual; --Return 3
SELECT InStr (' Syran Mo ', ' a ', up to) from dual; --Return 0
SELECT InStr (' Syranmo ', ' an ', -1,1) from dual; --Return 4
SELECT InStr (' abc ', ' d ') from dual; --Return 0

Second, other application examples:
1> from the Staff table query work number code is ' a10001′, ' a10002′ employees:
①select code, Name, dept, occupation from the staff WHERE code in (' A10001 ', ' A10002 ');
②select code, Name, dept, occupation from the staff WHERE code = ' A10001 ' OR code = ' A10002 ';
③select Code, name, dept, occupation from the staff WHERE InStr (' a10001,a10002 ', code) >0;
This usage is often used in stored procedures, and the range string is passed as a parameter. If you use ① 's SQL notation to pass the spelled string in direct use, the stored procedure will error. This is the way to find, the incoming spelling string can be used directly.

2> Fuzzy query:
①select code, Name, dept, occupation from the staff WHERE code like '%001% ';

②select Code, name, dept, occupation from the staff WHERE InStr (Code, ' 001 ') > 0;

Oracle InStr functions use