Use of Oracle InStr functions

In Oracle

You can use the InStr function to judge a string to determine whether it contains the specified character.

Finds the specified character in a string, returning the position of the specified character that was found.

Grammar:
InStr (Sourcestring,deststring,start,appearposition)

InStr (' Source string ', ' target string ', ' Start position ', ' first occurrence ')

Where sourcestring represents the source string;

Deststring represents the substring to be looked up from the source string;

Start represents the starting position of the lookup, which is optional and defaults to 1;

The Appearposition representative wants to find out the first occurrence of the deststring from the source character, which is also optional and defaults to 1

If the value of start is negative, the lookup 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 found string.


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

Sql>Select InStr (' abcdefgh ', ' de ') position from dual;


POSITION
----------
4
counting from 1 onwardsD row four so return 4

Sql>Select InStr (' Abcdefghbc ', ' BC ', 3) position from dual;

POSITION
----------
9
The 3rd character starting from the 3rd character is C, so find the BC after the 3 string, return 9

---------------------------
Starting with the 1th character, find the location of the 2nd occurrence of a substring
Sql>Select InStr (' Qinyinglianqin ', ' Qin ', 1, 2) position from dual;
POSITION
----------
A

----------------------------------------------------------------------

Use of Oracle InStr functions