index_of Function

The index_of function determines the position of the first character of the search string at its first occurrence, if any.

Syntax

returnvalue index_of(source, search_string [, start_position])

source ::= any*
search_string ::= any*
start_position ::= integer*
returnvalue ::= integer

Semantics

source

The input string to be searched. This argument is implicitly cast to a sequence of strings.

search_string

The string that should be searched in the source. This argument is implicitly cast to a sequence of strings.

start_position

An optional integer indicating, numerically, the position in the source from where the search should begin.

The default start_position is 0 which is also the position of the first character in the source.

If a negative value is supplied to start_position then 0 is assumed.

returnvalue

Returns the position of the first character of the search string at its first occurrence.

Returns -1 if search_string is not present in source.

Returns 0 for any value of source if the search_string is of length 0.

Returns NULL if any argument is NULL.

Returns NULL if any argument is an empty sequence or a sequence with more than one item.

Returns error if start_position argument is not an integer.

Note:

The returnvalue is relative to the beginning of source, regardless of the value of start_position.

Example 12-18 index_of Function

In this example, the index of "r" is selected in the firstname.

In the output, John has no occurrence of "r" so -1 is returned. Peter and Mary has "r" at 4 and 2 position respectively.

SELECT firstname, index_of(firstname,"r") FROM users;

 +-----------+----------+
 | firstname | Column_2 |
 +-----------+----------+
 | John      |       -1 |
 | Peter     |        4 |
 | Mary      |        2 |
 +-----------+----------+

Example 12-19 index_of Function

In this example, the index of "e" is selected in the firstname. In the output, notice that although "e" occurs twice in Peter, only the position of the first occurrence is returned.

SELECT firstname, index_of(firstname,"e") FROM users;

 +-----------+----------+
 | firstname | Column_2 |
 +-----------+----------+
 | John      |       -1 |
 | Peter     |        1 |
 | Mary      |       -1 |
 +-----------+----------+