Teradata has a variety of inbuilt functions to work with Strings. The INDEX function is one of the most commonly used functions in Teradata. It helps to identify the position of a character or string in the values.
The INDEX function accepts two arguments. The first one is the string expression and the second one is the char or string for which you want to find the position. The output of the index function is an integer that gives the position of the string.
Syntax
INDEX(string expression, string inside to find inside)
Let us understand this better with an example.
Imagine a weblogs table that has the URL of the websites that are browsed from a user machine. In that, let’s say you want to find the position of the word ‘google’ to know whether the URL is a google search.
URL – ‘https://www.google.com’
The following query will give you the position of the word ‘google’. And if the position is greater than zero, you can say that the word google is present in the string.
SELECT INDEX('https://www.google.com', 'google') as pos;
Output
Let us see another example in which the index function returns zero
URL – ‘https://www.gmail.com’
SELECT INDEX('https://www.gmail.com', 'google') as pos;
Output
In the above example, you can see that the word google is not present in the string expression. As a result, the INDEX function returned zero as the position of the word google. This confirms that the word google is not there in the input string.
Also read,