Searches character string char_expression1 for the character string char_expression2. The search begins at start_pos of char_expression1. If occurrence is specified, then INSTR searches for the nth occurrence, where n is the value of the fourth argument.

The position (with respect to the start of char_expression1) is returned if a search is successful. Zero is returned if no match can be found.

Syntax

INSTR ( char_expression1 , char_expression2[ , start_pos[ , occurrence]] )

Notes

  • The first and second arguments must be CHARACTER data type.
  • The third and fourth arguments, if specified, must be SMALLINT or TINYINT data type.
  • The value for start position in a character string is the ordinal number of the character in the string. The very first character in a string is at position 1, the second character is at position 2, the nth character is at position n.
  • If you do not specify start_pos, a default value of 1 is assumed.
  • If you do not specify occurrence, a default value of 1 is assumed.
  • The result is INTEGER data type.
  • If any of the argument expressions evaluate to NULL, the result is NULL.
  • A char_expression and the result can contain multi‑byte characters.

Example

This example illustrates the INSTR function:

SELECT cust_no, last_name
    FROM customer
    WHERE INSTR (LOWER (addr), 'heritage') > 0 ;

Compatibility

Progress extension