Oracle implementation indexof similar summary ____oracle

In Oracle/plsql, the InStr function returns the position of the string to be intercepted in the source string.

The syntax is as follows: InStr (string1, string2 [, Start_position [, Nth_appearance]])

String1 the source string to find in this string.

String2 the string to find in the string1.

The start_position represents where the string1 starts looking. This parameter is optional, if omitted defaults to 1. The string index starts at 1. If this parameter is positive, retrieves from left to right, if this parameter is negative, retrieves from right to left, and returns the starting index of the string to find in the source string.

The nth_appearance represents the string2 to find the first occurrence. This parameter is optional, and if omitted, the default is 1. If the system is negative, the error occurs.

Attention:

If String2 is not found in String1, the InStr function returns 0. Apply to:

Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

Select InStr (' abc ', ' A ') from dual; --Return 1
Select InStr (' abc ', ' BC ') from dual; --Return 2
Select InStr (' abc abc ', ' A ', 1,2) from dual; --Return 5
Select InStr (' abc ', ' BC ', -1,1) from dual; --Return 2
Select InStr (' abc ', ' d ') from dual; --Return 0

Substr and InStr nested to achieve indexof similar functionality

Eg: Garden Professional priority Tel: 07713212335-803 Get the telephone number, by ' Telephone: ' to be separated.

Select substr (' Garden Professional priority phone: 07713212335-803 ', InStr (' Garden Professional priority Tel: 07713212335-803 ', ' Tel: ') +3,20) from dual;