INSTR in Teradata

INSTR in Teradata is used to get the position of a search string in 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 the 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, 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 negative value specified in position, search will count and begin backward from end of the source_string.
• If the occurrence is specified,  search will begin at this position in the source_string.
• In case occurrence is not specified, search starts at the beginning of source_string.
• Occurrence can not be negative value or zero.