How to use instr method in oracle

In Oracle, the instr method is used to judge a string to determine whether it contains the specified character. This method can search for the specified character in a string and return the found specified character. The position, the syntax is "instr('source string','target string','starting position','number of occurrences')".

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How to use the instr method in Oracle

1. The concept of the instr function

In Oracle, you can use the instr function to judge a certain string , determine whether it contains the specified characters. Search for the specified character in a string and return the position of the specified character found.

2, syntax

instr(sourceString,destString,start,appearPosition) 
instr（'源字符串' , '目标字符串' ,'开始位置','第几次出现'）

where sourceString represents the source string;

destString represents the substring to be found from the source string;

start represents the starting position of the search. This parameter is optional and the default is 1;

appearPosition represents the number of occurrences of destString that you want to find from the source characters. This parameter is also optional. The default is 1

If the value of start is a negative number, it means searching from right to left, but the position data is still calculated from left to right.

The return value is: the position of the found string. If not found, 0 is returned.

Three, for example:

Find the position of a in abcd, start from the first letter, and find the position when it first appears (the last two Parameters can be omitted and the default is 1)

select instr('abcd','a',1,1) from dual; ---1
select instr('abcd','c',1,1) from dual; ---3
select instr('abcd','e',1,1) from dual; ---0

This function can be used for fuzzy query and judgment of inclusion relationships:

For example:

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%' ;
select ccn,mas_loc from mas_loc where instr('FH,FHH,FHM',ccn)>0;

Equivalent to

select ccn,mas_loc from mas_loc where ccn in ('FH','FHH','FHM');

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of How to use instr method in oracle. For more information, please follow other related articles on the PHP Chinese website!