How to Use instr functions in Oracle

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 to be searched in this string.
String2
The string to be searched in string1.
Start_position

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

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; -- return 1
SELECT instr ('syranmo', 'A') FROM dual; -- 3 is returned.
SELECT instr ('syran Mo', 'A', 1, 2) FROM dual; -- Return 0

(According to the condition, because a appears only once, the fourth parameter 2, that is to say, the location where a appears for the first time is obviously not displayed for the second time, So 0 is returned. Note that space is a single character !)

SELECT instr ('syranmo', 'any',-) FROM dual; -- 4 is returned.

(Even from the right to the left, the index position still depends on the location of the first letter on the left of 'any', SO 4 is returned here)

SELECT instr ('abc', 'D') FROM dual; -- returns 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. See the following example:

If I have a copy of the information, there are some employee ID (field: CODE), but now I want to query all their employees, such as names, departments, and occupations, here is an example of two employees with the employee IDs 'a10001' and 'a10002'. If the employee table is staff, 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 a lot of employees, and we think it is troublesome for that, so we thought, can we export it at one time? You can use the instr function as follows:

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

The query results are the same, so that only two single quotes are used before and after the query, which is relatively convenient.

Another usage is as follows:

SELECT code, name, dept, occupation FROM staff WHERE instr (code, '001')> 0;
Equivalent
SELECT code, name, dept, occupation FROM staff WHERE code LIKE '% 100 ';

Instances used by Oracle instr Functions

The format of the INSTR method is
INSTR (src, subStr, startIndex, count)
Src: Source string
SubStr: substring to be searched
StartIndex: starts with a few characters. A negative number indicates searching from right to left.
Count: the number of matched numbers to be found.
Return Value: the position of the substring in the string. The value 1st indicates 1. If the substring does not exist, the value 0. (Note: If src is a null string, the return value is null ).

Example:

The simplest one is to search for the l character. The first l is located at the 3rd position.
SQL> select instr ('hello, java world', 'L') from dual;

INSTR ('hello, javaworld', 'L ')
----------------------------
3

Search for characters starting from 4th.
SQL> select instr ('hello, java world', 'l', 4) from dual;
INSTR ('hello, javaworld', 'l', 4)
------------------------------
4

Search for 1st characters starting from 3rd
SQL> select instr ('hello, java world', 'l', 1, 3) from dual;
INSTR ('hello, javaworld', 'l', 1,
------------------------------
15

Search for 1 character, starting from 1st on the right and 3rd from right to left (that is, 1st from left to right)
SQL> select instr ('hello, java world', 'l',-1, 3) from dual;
INSTR ('hello, javaworld', 'l',-1
------------------------------
3
If no value is found, 0 is returned.
SQL> select instr ('hello, java world', 'mm') from dual;
INSTR ('hello, javaworld', 'mm ')
-----------------------------
0

Case where the source character is null''
Copy codeThe Code is as follows:
-- 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