Oracle InStr functions

INSTR

(Source string, target string, start position, match ordinal)

In Oracle/plsql, the InStr function returns the position of the string to intercept in the source string. Retrieve only once, that is, from the beginning of the character

Ends at the end of the character.

The syntax is as follows:

InStr (string1, string2 [, Start_position [, Nth_appearance]])

Parametric analysis:

String1

The source string to look up in this string.

string2

The string to find in the string1.

Start_position

Represents the location of the string1 to begin the search. This parameter is optional if omitted by default to 1. The string index starts at 1. If this parameter is positive, it is retrieved from left to right, and if this parameter is negative, right-to-left, returns the starting index of the string to find in the source string.

Nth_appearance

Represents the string2 to find the occurrence of the first few occurrences. This parameter is optional, and if omitted, the default is 1. If the system is negative, it will give an error.

Attention:

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

Example:

SELECT InStr (' Syranmo ', ' s ') from dual; --Return 1

SELECT InStr (' Syranmo ', ' RA ') from dual; --Return 3

1 SELECT InStr (' Syran Mo ', ' a ', up to) from dual; --Return 0

(depending on the condition, because a only occurs once, the fourth parameter 2, that is, the 2nd occurrence of a position, it is clear that the 2nd time is no longer appear, so the result returns 0.) Note that the space is also counted as a character! ）

SELECT InStr (' Syranmo ', ' an ', -1,1) from dual; --Return 4

(even from right to left, the position of the index is to look at the position of the first letter on the left side of ' an ', so this returns 4)

SELECT InStr (' abc ', ' d ') from dual; --Return 0

Note: This function can also be used to check if the String1 contains String2, if the return 0 means not included, otherwise the inclusion is represented.