8.6.2 INSTR

(1) Specification format

scalar-function-INSTR ::= INSTR(target-data,search-character-string[,search-start-position[,nth-occurrence]])
 
  target-data ::= value-expression
  search-character-string ::= value-expression
  search-start-position ::= value-expression
  nth-occurrence ::= value-expression

To Page Top

(2) Explanation of specification format

target-data:

Specifies the target data in which to search for the character string.

The following rules apply:

• Specify the target data in the form of a value expression. For details about value expressions, see 7.20 Value expression.

• Specify CHAR or VARCHAR type data for the target data.

• You cannot specify a dynamic parameter by itself for the target data.

search-character-string:

Specifies the character string to search for.

The following rules apply:

• Specify search-character-string in the form of a value expression. For details about value expressions, see 7.20 Value expression.

• Specify CHAR or VARCHAR type data for search-character-string.

• You cannot specify a dynamic parameter by itself for search-character-string.

search-start-position:

Specifies the starting character position to begin searching the target data.

• If you specify a positive integer for search-start-position

  The search starts at that position in the target data and proceeds in the forward direction (left to right). For example, if you specify 2 for search-start-position, the search starts at the second character of the target data and proceeds in the forward direction (left to right).

  Example: INSTR('AB01AB02AB03','AB',2) → 5

  

• If you specify a negative integer for search-start-position

  The search starts at that position from the end of the target data and proceeds backwards (right to left). For example, if you specify -2 for search-start-position, the search starts at the second character from the end of the target data and proceeds backwards (right to left).

  Example 1: INSTR('AB01AB02AB03','AB',-2) → 9

  

  Example 2: INSTR('AB01AB02AB03','AB',-4) → 9

  

  In the above example, the search begins at A, which is immediately followed by B. The execution results is therefore 9.

The following rules apply:

• Specify search-start-position in the form of a value expression. For details about value expressions, see 7.20 Value expression.

• Specify INTEGER or SMALLINT type data for search-start-position.

• If search-start-position is omitted, 1 is assumed.

• If you specify a dynamic parameter by itself for search-start-position, the assumed data type of the dynamic parameter is INTEGER.

nth-occurrence:

Specifies which occurrence of the character string to search for. For example, if you specify 3 for nth-occurrence, it returns the starting position of the third occurrence of the character string in the target data.

The following rules apply:

• Specify nth-occurrence in the form of a value expression. For details about value expressions, see 7.20 Value expression.

• Specify a positive integer for nth-occurrence.

• Specify INTEGER or SMALLINT type data for nth-occurrence.

• If nth-occurrence is omitted, 1 is assumed.

• If you specify a dynamic parameter by itself for nth-occurrence, the assumed data type of the dynamic parameter is INTEGER.

The following examples illustrate the result of executing the scalar function INSTR.

Examples

• INSTR('AB01AB02AB03','AB') → 1

  This example returns 1 because the search character string 'AB' is found at the first position in the target data.

• INSTR('AB01AB02AB03','AB',3) → 5

  In this example, the search begins at the third character from the beginning of the target data. It returns 5 because the search character string 'AB' is found at the fifth position in the target data.

• INSTR('AB01AB02AB03','AB',3,2) → 9

  In this example, the search begins at the third character from the beginning of the target data. Furthermore, because nth-occurrence is 2, it returns the starting position of the second occurrence of 'AB', which in this case is 9 because the second occurrence of 'AB' starts at the ninth character of the target data.

• INSTR('AB01AB02AB03','AB',-2,3) → 1

  In this example, the search begins at the second character from the end of the target data. Furthermore, because nth-occurrence is 3, it returns the starting position of the third occurrence of 'AB', which in this case is 1 because the third occurrence of 'AB' starts at the first character of the target data.

To Page Top

(3) Rules

1. The value of the execution result is expressed in units of number of characters.

2. Regardless of the value of search-start-position, the value returned as the execution result will be the position of the occurrence of the character string as counted from the beginning of the target data (from the left).

3. If the specified character string is not found, 0 is returned as the value of the execution result.

4. The data type of the execution result is the INTEGER type.

5. The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).

6. In the following cases, the execution result will be a null value:

   • If either target-data, search-character-string, search-start-position, or nth-occurrence has a null value

   • If you specify 0 for search-start-position

   • If you specify 0 or a negative value for nth-occurrence

7. If either target-data or search-character-string has an actual length of 0 bytes or 0 characters, the value of the execution result will be 0, except in the following cases:

   • If either target-data or search-character-string has a null value

   • If you specify 0 for search-start-position

   • If you specify 0 or a negative value for nth-occurrence

8. The character strings search-character-string and target-data are compared character-by-character until either the nth occurrence (specified in nth-occurrence) of search-character-string is found, or the end of target-data is reached.

To Page Top

(4) Example

Example:

From the email addresses stored in column C1 of table T1, extract the character string preceding the @ part of each address.

SELECT LEFT("C1",INSTR("C1",'@')-1) FROM "T1"

To Page Top