POSSTR

The POSSTR function returns the starting position of the first occurrence of one string (called the search-string) within another string (called the source-string). If the search-string is not found and neither argument is null, the result is zero. If the search-string is found, the result is a number from 1 to the actual length of the source-string.

See the related functions, LOCATE and POSITION.

source-string: An expression that specifies the source string in which the search is to take place. source-string may be any built-in numeric, datetime, or string expression. A numeric or datetime argument is cast to a character string before evaluating the function. For more information about converting numeric and datetime to a character string, see VARCHAR.
search-string: An expression that specifies the string that is to be searched for. search-string may be any built-in numeric, datetime, or string expression. It must be compatible with the source-string. A numeric or datetime argument is cast to a character string before evaluating the function. For more information about converting numeric and datetime to a character string, see VARCHAR.

The result of the function is a large integer. If either of the arguments can be null, the result can be null. If either of the arguments is null, the result is the null value.

The POSSTR function accepts mixed data strings. However, POSSTR operates on a strict byte-count basis without regard to single-byte or double-byte characters.¹ It is recommended that if either the search-string or source-string contains mixed data, POSITION should be used instead of POSSTR. The POSITION function operates on a character basis. In an EBCDIC encoding scheme, any shift-in and shift-out characters are not required to be in exactly the same position, and their only significance is to indicate which characters are SBCS and which characters are DBCS.

If the CCSID of the search-string is different than the CCSID of the source-string, it is converted to the CCSID of the source-string. If the CCSID of the source-string is mixed data or UTF-8, CCSID conversion to UTF-16 will occur.

If a collating sequence other than *HEX is in effect when the statement that contains the POSSTR function is executed and the arguments are SBCS data, mixed data, or Unicode data, then the result is obtained by comparing weighted values for each value in the set. The weighted values are based on the collating sequence. An ICU collating sequence table cannot be specified with the POSSTR function.

If the search-string has a length of zero, the result returned by the function is 1. Otherwise:

if the source-string has a length of zero, the result returned by the function is 0.
Otherwise,
- If the value of search-string is equal to an identical length of substring of contiguous positions within the value of source-string, then the result returned by the function is the starting position of the first such substring within the source-string value.
- Otherwise, the result returned by the function is 0.²

Note

Start of change Syntax alternatives: STRPOS is a synonym for POSSTR. End of change

Example

Select RECEIVED and SUBJECT columns as well as the starting position of the words 'GOOD' within the NOTE_TEXT column for all entries in the IN_TRAY table that contain these words.
```
  SELECT RECEIVED, SUBJECT, POSSTR(NOTE_TEXT, 'GOOD')
    FROM IN_TRAY
    WHERE POSSTR(NOTE_TEXT, 'GOOD') <> 0
```

¹ For example, in an EBCDIC encoding scheme, if the source-string contains mixed data, the search-string will only be found if any shift-in and shift-out characters are also found in the source-string in exactly the same positions.

² This includes the case where the search-string is longer than the source-string.