INSTR function

The INSTR function searches a character string for a specified substring, and returns the character position in that string where an occurrence of that a substring ends, based on a count of substring occurrences.

The INSTR function has this syntax:


INSTR function

|--INSTR--(--source_string--,--substring--+------------------------+--)--|
                                          '-,--start--+----------+-'      
                                                      '-,--count-'

Element	Description	Restrictions	Syntax
count	Expression that evaluates to an integer `> 0`	Must be an expression, constant, column, or host variable of a built-in integer type, or that can be converted to an integer.	Expression
source_ string	Expression that evaluates to a character string	Must be an expression, constant, column, or host variable of a built-in character data type, or that can be converted to a character type	Expression
start	Ordinal position to begin the search in source_string, where 1 is the first logical character	Must be an expression, constant, column, or host variable of a built-in integer type, or that can be converted to a positive or negative integer	Expression
substring	Expression that evaluates to a character string	Must be an expression, constant, column, or host variable of a built-in character data type, or that can be converted to a character type	Expression

Arguments to INSTR cannot be user-defined data types.

The function returns NULL in each of these cases:

count is less than or equal to zero (0 ).
source_string is NULL or of zero length.
substring is NULL or of zero length.

The return value is zero (0 ) in each of the following cases:

if no occurrences of substring are found in source_string,
if start is greater than the length of source_string.
If fewer than count occurrences of substring are in the source_string,

If you omit the optional count argument, the default count value is 1.

In locales that support multibyte character sets, the return value is the ordinal value among logical characters in the source_string. In single-byte locales, such as the default locale, the return value is equivalent to the byte position, where the first byte is in position 1.

The start position

If start is omitted or is specified as zero, the search for substring begins at character position 1. If start is negative, the search for occurrences of substring begins at the end of source_string, and proceeds towards the beginning.

In a left-to-right locale, a negative start value specifies a right-to-left search.
In a right-to-left locale, a negative start value specifies a left-to-right search.

In both types of locales, however, the search begins at the logical character position within source_string that corresponds to the absolute value of start.

In a right-to-left locale, a negative start value specifies a left-to-right search.

Examples of INSTR function expressions

The following expressions are all based on the same source_string and substring. This example returns 3, as the character position of the first 'er' substring:

INSTR("wwerw.ibm.cerom", "er")

In the example above, both start and count default to 1.

The nest example starts the search at the 2nd character position, with a default count of 1:

INSTR("wwerw.ibm.cerom", "er", 2)

The expression above returns 3, the position of the first character in the first 'er' substring that a left-to-right search encounters.

The next example specifies a count of 2, starting the search in the first character of the source_string:

INSTR("wwerw.ibm.cerom", "er", 1, 2)

The expression above returns 12, the character position where the second 'er' begins.

The following example specifies -5 as the starting position, and the count specifies the first occurrence of "er" between the 5th position and the beginning of the source_string:

INSTR("wwerw.ibm.cerom", "er", -5, 1)

This returns 3, corresponding to the occurrence of the "er" substring that begins in that position. The negative start argument specifies a right-to-left search, but the return value is 3, because the reading direction of strings and substrings in the default locale is left-to-right.