INSTR

Searches string for substring and returns an integer indicating the position of the character in string that is the first character of this occurrence.

Searches *string *for *substring *and returns an integer indicating the position of the character in *string *that is the first character of this occurrence. The return value is based on the character position of the identified character.

Behavior type

Immutable

Syntax

INSTR ( string , substring [, position [, occurrence ] ] )

Arguments

string: (CHAR or VARCHAR, or BINARY or VARBINARY) Text expression to search.
substring: (CHAR or VARCHAR, or BINARY or VARBINARY) String to search for.
position: Nonzero integer indicating the character of string where Vertica begins the search. If position is negative, then Vertica counts backward from the end of string and then searches backward from the resulting position. The first character of string occupies the default position 1, and position cannot be 0.
occurrence: Integer indicating which occurrence of string Vertica searches. The value of occurrence must be positive (greater than 0), and the default is 1.

Notes

Both position and occurrence must be of types that can resolve to an integer. The default values of both parameters are 1, meaning Vertica begins searching at the first character of string for the first occurrence of substring. The return value is relative to the beginning of string, regardless of the value of position, and is expressed in characters.

If the search is unsuccessful (that is, if substring does not appear *occurrence *times after the position character of string, the return value is 0.

Examples

The first example searches forward in string ‘abc’ for substring ‘b’. The search returns the position in ‘abc’ where ‘b’ occurs, or position 2. Because no position parameters are given, the default search starts at ‘a’, position 1.

=> SELECT INSTR('abc', 'b');
 INSTR
-------
     2
(1 row)

The following three examples use character position to search backward to find the position of a substring.

Note

Although it might seem intuitive that the function returns a negative integer, the position of n occurrence is read left to right in the sting, even though the search happens in reverse (from the end—or right side—of the string).

In the first example, the function counts backward one character from the end of the string, starting with character ‘c’. The function then searches backward for the first occurrence of ‘a’, which it finds it in the first position in the search string.

=> SELECT INSTR('abc', 'a', -1);
 INSTR
-------
     1
(1 row)

In the second example, the function counts backward one byte from the end of the string, starting with character ‘c’. The function then searches backward for the first occurrence of ‘a’, which it finds it in the first position in the search string.

=> SELECT INSTR(VARBINARY 'abc', VARBINARY 'a', -1);
 INSTR
-------
     1
(1 row)

In the third example, the function counts backward one character from the end of the string, starting with character ‘b’, and searches backward for substring ‘bc’, which it finds in the second position of the search string.

=> SELECT INSTR('abcb', 'bc', -1);
 INSTR
-------
     2
(1 row)

In the fourth example, the function counts backward one character from the end of the string, starting with character ‘b’, and searches backward for substring ‘bcef’, which it does not find. The result is 0.

=> SELECT INSTR('abcb', 'bcef', -1);
INSTR
-------
     0
(1 row)

In the fifth example, the function counts backward one byte from the end of the string, starting with character ‘b’, and searches backward for substring ‘bcef’, which it does not find. The result is 0.

=> SELECT INSTR(VARBINARY 'abcb', VARBINARY 'bcef', -1);
INSTR
-------
     0
(1 row)

Multibyte characters are treated as a single character:

=> SELECT INSTR('aébc', 'b');
 INSTR
-------
     3
(1 row)

Use INSTRB to treat multibyte characters as binary:

=> SELECT INSTRB('aébc', 'b');
  INSTRB
--------
      4
(1 row)