INDEX Function

Returns the starting column position of a substring within a string.

Syntax

INDEX(string, substring, occurrence)

Syntax Elements

string An expression that evaluates to the string to be searched.

substring An expression that evaluates to the substring to be located.

occurrence An expression that evaluates to an integer that specifies the required occurrence of the substring.

Note

Substrings can overlap in the string. For example, the second occurrence of substring AA in string AAAA starts at the second character, not the third.

This behaviour can be modified with the UNIQUE.COUNT compatibility switch option.

Comments

If substring is not found, a value of zero is returned.

If substring is null, the specified occurrence number is returned.

Case Insensitivity

If data case insensitive mode is selected (see Case Sensitivity), case is ignored when searching string for substring.

Examples

START = INDEX("ABCDEFGHI","DEF",1)

Assigns the value 4 to the variable START.

A = INDEX("CCXXCCXXCC","XX",2)

Assigns the value 7 to A, because the second occurrence of substring XX starts at column 7.

VAR = INDEX("ABC123","Z",1)

Assigns a value of 0 to VAR, because Z is not present in the string ABC123.

X = "1234ABC"
Y = "ABC"
IF INDEX(X,Y,1)=5 THEN GOTO 3

Transfers control to statement 3, because ABC starts at column position 5 of string X.

S = "X1XX1XX1XX"
FOR I=1 TO INDEX(S,"1",3)
    .
    .
    .
NEXT I

Executes the loop 8 times, because the third occurrence of 1 appears in column 8 of string S.