The use of oracle instr function

In Oracle

You can use the instr function to determine whether a string contains a specified character.

Finds the specified character in a string and returns the position of the specified character found.

Syntax:

Instr (sourceString,destString,start,appearPosition)

Instr ('source string', 'target string', 'start position', 'occurrence number')

Where sourceString represents the source string

DestString represents the substring to find from the source string

Start represents the start of the search. This parameter is optional and defaults to 1.

AppearPosition represents the number of times you want to find the destString from the source character. This parameter is also optional. The default is 1.

If the value of start is negative, it means that the search is made from right to left, but the location data is still calculated from left to right.

The return value is the location of the string found.

For the instr function, we often use this: find the location of a specified substring from a string. For example:

SQL > select instr ('abcdefgh','de') position from dual

POSITION

-

four

D ranks fourth from 1, so return 4

SQL > select instr ('abcdefghbc','bc',3) position from dual

POSITION

-

nine

The third character is c from the third character, so the string after 3 looks for bc and returns 9.

-

Start with the first character and find the location of the second occurrence of the substring

SQL > select instr ('qinyinglianqin','qin', 1,2) position from dual

POSITION

-

twelve