INSTR in Teradata
Teradata INSTR function finds the occurrence of a specific find_string within a targeted super_string.
SELECT INSTR('What is Teradata','T');
9
Definition and Usage
Teradata INSTR function finds the occurrence of find_string within super_string.
Syntax
INSTR (super_string, find_string, starting_index, no_of_occurrence);
Argument Types
| Argument | Description |
|---|---|
| super_string | It is the main string where we find the occurrence of find_string. |
| find_string | A set of characters the function searches for. |
| starting_index | The starting position from where to start finding the occurrence of find_string.If this value is omitted it is assumed to be 1. |
| no_of_occurrence | Which occurrence to search for. Basically, the find_string may appear more than once in the super_string i.e. more than one occurrences of the find_string. In this case, we have to specify which occurrence we are searching for. The value might be equal to or greater than 1. |
Result Value
This function returns a NUMBER. This number represents the position of the occurrence of the find_string.
Examples
Example 1:
The INSTR function returns 20 in the following query. The INSTR function starts searching from 3 position and finds the second occurrence of 'ch' within the string.
SELECT INSTR('choose a chocolate chip cookie','ch',3,2);
Result: 20
Example 2:
The INSTR function searches for 'n' in the source string and returns 9 in the following query.
SELECT INSTR('What is Teradata','T');
Result: 9
Summary
You may use the INSTR function to find any of the occurrences of some string.
Was this article helpful?
