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.
SELECT cust_no, name
WHERE INSTR (LOWER (addr), 'heritage') > 0 ;
The first and second arguments must be of character type.
The third and fourth arguments, if specified, must be of type INTEGER.
If one of the arguments is a literal and the other one a field reference, the operation is possible only if the literal is convertible to the character set of the field reference. Otherwise an error is returned.
The values for specifying position in a character string starts from one. That is, the very first character in a string is at position one, the second character is at position two and so on.
If the third argument is not specified, a default value of one is assumed.
If the fourth argument is not specified, a default value of one is assumed.
The result is of type INTEGER.
If any of the argument expressions evaluates to null, the result is null.