INSTR

Purpose

This function returns the position in string at which search_string appears. If this is not contained, the value 0 is returned.

Syntax

instr::=

Usage Notes

  • The optional parameter position defines the search direction and at which position the search shall start:

    • If position is positive, the string is searched from the beginning to the end, starting at the character position counted from the beginning (the first character has position 1).

    • If position is negative, the string is searched from the end to the beginning, starting at character position counted from the end (the last character has position -1). The return value is counted from the beginning to the end, so the function will never return a negative value.

      Example: INSTR(string,'abc',-3) will search backwards starting from the third last character in the string.

  • The optional positive number occurrence defines which occurrence shall be searched for.

  • INSTR(string,search_string) is similar to INSTR(string,search_string,1,1).

  • The functions POSITION and LOCATE are similar.

Example

SELECT INSTR('abcabcabc','cab') INSTR1,
       INSTR('user1,user2,user3,user4,user5','user', -1, 2) INSTR2;
Result
INSTR1 INSTR2
3 19