INSTR in Teradata is used to get the position of a search string in the source string.
Syntax:
select instr( source_string, search_string [,position[,occurrence]])
Example:
SELECT INSTR('choose a chocolate chip cookie','ch',2,2);
The above query will return 20, indicating the position of string ‘ch’ in ‘chip’. This is the second occurrence of ‘ch’ with the search starting from the second position of the source string.
Output
20
Example:
SELECT INSTR('INSTR FUNCTION','N');
The above query returns result 2, which indicates the position of the first occurrence of ‘N’ in the source string with the search starting from the beginning of the string.
Output
2
INSTR in Teradata features:
- If the position is specified, the search will begin at this position in the source_string.
- Search will start at the beginning of the source string if the position is not specified.
- In case of the negative value specified in position, the search will count and begin backward from the end of the source_string.
- If the occurrence is specified, the search will begin at this position in the source_string.
- In case occurrence is not specified, the search starts at the beginning of source_string.
- Occurrence can not be a negative value or zero.