4.9.2.38 INSTR function (extension)

Syntax

INSTR ( char_expression, char_expression
                 [, start_position [, occurrence]])

Description

The scalar function INSTR searches for the character string corresponding to the second argument in the character string corresponding to the first argument starting at start_position. 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 string corresponding to the first argument) is returned if a search is successful. Zero is returned if no match can be found.

Example

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

Notes

• The first and second arguments must be of type CHARACTER.
• The third and fourth arguments, if specified, must be of type INTEGER.
• The values for specifying position in a character string starts from 1. That is, the very first character in a string is at position 1, the second character is at position 2 and so on.
• If the third argument is not specified, a default value of 1 is assumed.
• If the fourth argument is not specified, a default value of 1 is assumed.
• The result is of type INTEGER.
• If any of the argument expressions evaluates to null, the result is null.