INSTRB

Searches string for substring and returns an integer indicating the octet position within string that is the first occurrence. The return value is based on the octet position of the identified byte.

Behavior type

Syntax

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

Arguments

string

Is the text expression to search.

substring

Is the string to search for.

position

Is a 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 byte of string occupies the default position 1, and position cannot be 0.

occurrence

Is an 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 byte 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 octets.

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

Examples

=> SELECT INSTRB('straße', 'ß');
 INSTRB
--------
      5
(1 row)

See also

• INSTR