POSSTR or STRPOS scalar function

The POSSTR function returns the position of the first occurrence of an argument within another argument.

The schema is SYSIBM.

If search-string is not found and neither argument is null, the result is 0. If search-string is found, the result is a number from 1 to the actual length of source-string.

source-string: An expression that specifies the source string in which the search is to take place. source-string must return a value that is a built-in character string data type, graphic string data type, or binary string data type.
The argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type.
search-string: An expression that specifies the string for which to search. search-string must return a value that is a built-in character string data type, graphic string data type, or binary string data type with an actual length that is no greater than 4000 bytes.
The argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type.

The first and second arguments must have compatible string types. For more information on compatibility, see Conversion rules for comparisons.

If the search-string and source-string have different CCSID sets, then the search-string is converted to the CCSID set of the source-string.

Both search-string and source-string have zero or more contiguous positions. For character strings and binary strings, a position is a byte. For graphic strings, a position is a DBCS character. Graphic Unicode data is treated as UTF-16 data; a UTF-16 supplementary character takes two DBCS characters to represent and as such is counted as two DBCS characters.

The strings can contain mixed data.

For ASCII data, if search-string or source-string contains mixed data, search-string is found only if the same combination of single-byte and double-byte characters are found in source-string in exactly the same positions.
For EBCDIC data, if search-string or source-string contains mixed data, search-string is found only if any shift-in or shift-out characters are found in source-string in exactly the same positions, ignoring any redundant shift characters.
For UTF-8 data, if search-string or source-string contains mixed data, search-string is found only if the same combination of single-byte and multi-byte characters are found in source-string in exactly the same position.

If none of the above conditions are met, the result is 0.

The result of the function is a large integer. The value of the result is determined by applying these rules in the order in which they appear:

If the length of search-stringis zero, the result is 1.
If the length of source-string is zero, the result is 0.
If the value of search-string is equal to an identical length substring of contiguous positions from the value of source-string, the result is the starting position of the first such substring within the value of source-string.

The result can be null; if any argument is null, the result is the null value.

Notes

Syntax alternatives:: STRPOS is a synonym for POSSTR.
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.

Examples

Example: Select the RECEIVED column, the SUBJECT column, and the starting position of the string 'GOOD BEER' within the NOTE_TEXT column for all rows in the IN_TRAY table that contain that string.

   SELECT RECEIVED, SUBJECT, POSSTR(NOTE_TEXT, 'GOOD BEER')
     FROM IN_TRAY
     WHERE POSSTR(NOTE_TEXT, 'GOOD BEER') <> 0;