POSSTR

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


>>-POSSTR(source-string,search-string)-------------------------><

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.

Start of change The argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type. End of change

The expression can be specified by any of the following items:

A constant
A special register
A host variable (including a LOB locator variable or a file reference variable)
A scalar function whose arguments are any of the above (though nested function invocations cannot be used)
A column name
A CAST specification whose arguments are any of the above
An expression that concatenates (using CONCAT or ||) any of the above

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.

Start of change The argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type. End of change

The expression can be specified by any of the following items:

A constant
A special register
A host variable (including a LOB locator variable or a file reference variable)
A scalar function whose arguments are any of the above (though nested function invocations cannot be used)
A CAST specification whose arguments are any of the above
An expression that concatenates (using CONCAT or ||) any of the above

These rules are similar to those that are described for pattern-expression for the LIKE predicate.

Start of change A column name cannot be specified for search-string, except in some cases where the database manager rewrites the SQL and replaces the column name with a literal value or a variable. End of change

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.

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.

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.
If none of the above conditions are met, the result is 0.

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

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;