INDEX function in Teradata Extension - Teradata

Compatibility: Teradata Extension

The INDEX function is used to return a number that represents the starting position of a specified character string with character data. To use the INDEX function, specify the name of the column containing the data to examine and the character string to find. It returns a numeric value that is the first occurrence of the character string. If the character string is not found, a zero is returned to indicate that the string does not exist.

The INDEX function is the original Teradata function to search for a character string within data. However, POSITION is the ANSI standard.

The syntax of the INDEX follows:

INDEX ( <column-name>, <character-string> )

It is common to use the INDEX function in the WHERE clause as well as in the SELECT list. This provides the ability to return data based on the presence or absence of a character string within the data.

Example of using the INDEX function:

1 Row Returned (in Teradata Mode)Example of using the INDEX function 1 Row Returned (in ANSI Mode)Example of using the INDEX function

The INDEX function has been available in Teradata for a long time. This function worked before there was a defined standard command to provide this functionality. Today, it is recommended to use the POSITION function instead of the INDEX function because it is now the ANSI standard. However, I have seen situations when using POSITION inside the SUBSTRING did not work and INDEX did, as an alternative.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Teradata Topics