Oracle instr function usage

INSTR (source string, destination string, starting position, matching ordinal)

In Oracle/PLSQL, the instr function returns the position of the string to be intercepted in the source string. Retrieve 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
Source string to look up in.
string2
The string to look for in string1.
start_position

Represents where to start the search for string1. This parameter is optional. If omitted, the default is 1. The string index starts at 1. If this parameter is positive, retrieval starts from left to right, and if this parameter is negative, retrieval from right to left returns the starting index of the string to be searched in the source string.

nth_appearance

Represents the number of occurrences of string2 to be found. This parameter is optional. If omitted, the default is 1. The system will report an error if it is negative.

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','ra') FROM dual; - return to 3
SELECT instr('syran mo','a',1,2) FROM dual; - return 0

(According to the condition, since a only appears once, the fourth parameter 2, that is, the position of a appears the second time, obviously, the second time does not appear again, so the result returns 0. Note that Spaces count as 1 character!)

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

(Even from right to left, the position of the index depends on the left first letter of 'an', so return 4.)

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

Note: This function can also be used to check whether String1 contains String2. If 0 is returned, it means no; otherwise, it means yes.
For the above, we can use the instr function this way. See the following example:

If I have a file with 1 employee's job number (field: CODE), and I want to find out all their employee information, such as name, department, occupation, etc. Here are two employees with job number 'A10001 'and 'A10002', assuming that staff is the employee table, then 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 there are more employees, and we feel a little bit bothered about that, so we think, can we derive it once? You can use the instr function as follows:

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

Query the result 1, so before and after using only two single quotes, relatively convenient point.

Another usage is as follows:

SELECT code, name, dept, occupation FROM staff WHERE instr(code, '001') > 0;
Is equivalent to
SELECT code, name, dept, occupation FROM staff WHERE code LIKE '%001%' ;

instr's instr function use instance

The format of the INSTR method is
INSTR(src, subStr,startIndex, count)
src: Source string
subStr: The substring to find
startIndex: Start with which character. Negative numbers mean looking from right to left.
count: Which serial number to match
Return value: The position of a substring in a string, the first being 1; There is no 0. (Special note: if src is empty, the return value is null).

Examples of usage:

The simplest is the l character, with the first l in the third position.
SQL > select instr('hello,java world', 'l') from dual;

INSTR('HELLO,JAVAWORLD','L')
----------------------------
3

Look for the l character, starting at the fourth position.
SQL > select instr('hello,java world', 'l', 4) from dual;
INSTR('HELLO,JAVAWORLD','L',4)
------------------------------
4

Find the l character, the third from the first position
SQL > select instr('hello,java world', 'l', 1, 3) from dual;
INSTR('HELLO,JAVAWORLD','L',1,
------------------------------
15

Look for l characters, starting at the first position on the right, and looking for the third from the right (that is, the first from the left to the right)
SQL > select instr('hello,java world', 'l', -1, 3) from dual;
INSTR('HELLO,JAVAWORLD','L',-1
------------------------------
3
Could not find return 0
SQL > select instr('hello,java world', 'MM') from dual;
INSTR('HELLO,JAVAWORLD','MM')
-----------------------------
0

When the source character is an empty string ''

 
-- Created on 2010-12-22 by CHEN 
declare 
-- Local variables here 
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