Instr () function in Oracle

I. Format of instr() function (commonly known as character search function)

Format 1: instr( string1, string2 )

/ instr(source string, destination string)

Format 2: instr( string1, string2 [, start_position [, nth_appearance ] ] )

/ instr(source string, destination string, start position, match ordinal)

Parsing: The value of string2 is to be found in string1, starting from the value given by start_position (i.e.: position) and searching string1 to retrieve the nth_appearance (how many) times string2 appears.

Note: In Oracle/PLSQL, the instr function returns the position of the string to intercept in the source string. Search only once, that is, from the beginning of the character to the end of the character.

II. Examples

form in

1. select instr ('helloworld ',' l') from dual; --default to the location where the "l" first appears

2. select instr ('helloworld ',' lo') from dual; --in "lo", where the "l" begins

3. select instr ('helloworld ','wo') from dual; --where "w" begins to appear

Format II

1、select instr('helloworld','l',2,2) from dual;

--Start at position 2(e) of helloworld and find the position of the second occurrence of "l"

2、select instr('helloworld','l',3,2) from dual;

--Start at position 3(l) of helloworld and find the position of the second occurrence of "l"

3、select instr('helloworld','l',4,2) from dual;

--Start at position 4(l) of helloworld and find the position of the second occurrence of "l"

4、select instr('helloworld','l',-1,1) from dual;

--Start at the 1(d) last position of "helloworld" and look back to the position of the first occurrence of "l"

5、select instr('helloworld','l',-1,2) from dual;

--Start at the penultimate 1(d) position of "helloworld" and look back for the second occurrence of "l"

6、select instr('helloworld','l',2,3) from dual;

--Start at position 2(e) of helloworld and find the position of the third occurrence of "l"

7、select instr('helloworld','l',-2,3) from dual;

--Start at the penultimate position of "helloworld" and look back to the position of the third occurrence of "l"

Note: Fuzzy query like and instr() functions have the same query effect:

select from table where colName like '%helloworld%';

select from table where instr(colName ,'helloworld')>0; --The two statements have the same effect

Example: