INSTR (source string, target string, start position, match ordinal)
In Oracle/plsql, the InStr function returns the position of the string to be intercepted in the source string. Retrieves only once, that is, from the beginning of the character to the end of the character.
The syntax is as follows:
InStr (string1, string2 [, Start_position [, Nth_appearance]])
Parameter analysis:
String1
The source string to find in this string.
string2
The string to find in the string1.
Start_position
Represents which location of the string1 to start the lookup. This parameter is optional, if omitted defaults to 1. The string index starts at 1. If this parameter is positive, retrieves from left to right, if this parameter is negative, retrieves from right to left, and returns the starting index of the string to find in the source string.
Nth_appearance
Represents the string2 to find the first occurrence. This parameter is optional, and if omitted, the default is 1. If the system is negative, the error occurs.
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
SELECT InStr (' Syran Mo ', ' a ', 1,2) from dual; --Return 0
(according to the condition, because a only occurs once, the fourth parameter 2, that is, the 2nd time appears the position of a, apparently the 2nd time is no longer appears, so the result returns 0.) Note that the space is also counted as a character! ）
SELECT InStr (' Syranmo ', ' a ', -1,1) from dual; --Return 4
(even if it's 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: This function can also be used to check whether the String1 contains String2, or to include if the return 0 indicates that it is not included.
For the above mentioned, we can use the InStr function in this way. Take a look at the following example:
If I had a piece of information, above are some employee's work number (field: CODE), but I now have to check out all their employees, such as name, department, occupation, etc., here for example two employees, the work number is ' a10001′, ' a10002′, which assumes that staff is the employee table, The normal practice is as follows:
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 the staff is more, we to that ' feel more trouble, so think, can export it 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 result, so that only two times before and after the single quotation mark, relatively convenient point.
There is another usage, as follows:
SELECT code, Name, dept, occupation from staff WHERE InStr (Code, ' 001 ') > 0;
Equal to
SELECT code, Name, dept, occupation from staff WHERE code like '%001% ';
Oracle's InStr function usage Example
The format of the InStr method is
INSTR (SRC, substr,startindex, count)
SRC: source string
SUBSTR: substring to find
StartIndex: Start with the first few characters. A negative number indicates a lookup from right to left.
Count: To find ordinal number of matches
Return value: The position of the substring in the string, the 1th is 1; it does not exist as 0. (Special note: If SRC is an empty string, the return value is null).
Use examples:
The simplest one, find the L character, and the first L is in position 3rd.
Sql> Select InStr (' Hello,java World ', ' l ') from dual;
INSTR (' Hello,javaworld ', ' L ')
----------------------------
3
Find the L character, starting at position 4th.
Sql> Select InStr (' Hello,java World ', ' l ', 4) from dual;
INSTR (' Hello,javaworld ', ' L ', 4)
------------------------------
4
Find L character, 3rd from 1th position
Sql> Select InStr (' Hello,java World ', ' l ', 1, 3) from dual;
INSTR (' Hello,javaworld ', ' L ', 1,
------------------------------
15
Find the L character, start from the 1th position on the right, and look for the 3rd from right to left (the 1th from left to right)
Sql> Select InStr (' Hello,java World ', ' l ',-1, 3) from dual;
INSTR (' Hello,javaworld ', ' L ',-1
------------------------------
3
Unable to find return 0
Sql> Select InStr (' Hello,java World ', ' MM ') from dual;
INSTR (' Hello,javaworld ', ' MM ')
-----------------------------
0
The case where the source character is an empty string '
Copy Code code as follows:
--Created on 2010-12-22 by CHEN
Declare
--Local variables
I varchar2 (2);
Begin
--Test statements here
I: = InStr (', ', ', ');
If I is null then
Dbms_output.put_line (' I is empty ');
End If;
End
Result output:
I is empty
