Try OpenEdge Now
skip to main content
SQL Reference
SQL Reference : OpenEdge SQL Functions : INSTR
 

INSTR

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