POSSTR scalar function

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).

The schema is SYSIBM.

Numbers for the search-string position start at 1 (not 0).

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.

source-string

An expression that specifies the string to be searched through. This expression must return a built-in character string, numeric value, Boolean value, or datetime value. If the value is not a character string, it is implicitly cast to VARCHAR before the function is evaluated.

search-string

An expression that specifies the string that is to be searched for. This expression must return a value that is a built-in string, signed numeric, Boolean, or datetime value. If the value is not a string, it is implicitly cast to VARCHAR before the function is evaluated. The actual length must not exceed the maximum length of a VARCHAR.

The expression cannot include any of the following elements (SQLSTATE 42824):

A LOB file reference variable
A parameter of an inlined SQL user-defined function
A transition variable in an inlined trigger
A local variable in a compound SQL (inlined) statement
A user-defined function
A non-deterministic built-in function
A scalar fullselect

In a Unicode database, if one argument is character (not FOR BIT DATA) and the other argument is graphic, then the search-string is converted to the data type of the source-string for processing. If one argument is character FOR BIT DATA, the other argument must not be graphic (SQLSTATE 42846).

Both search-string and source-string have zero or more contiguous positions. If the strings are character or binary strings, a position is a byte. If the strings are graphic strings, a position is a double byte. POSSTR operates on a strict byte-count basis, without awareness of either the database collation or changes between single and multi-byte characters. The POSITION, LOCATE, or LOCATE_IN_STRING functions can be used to operate with awareness of the database collation and the string units.

The following rules apply:

The data types of source-string and search-string must be compatible, otherwise an error is raised (SQLSTATE 42884).
- If source-string is a character string, then search-string must be a character string, but not a CLOB, with an actual length of 32672 bytes or less.
- If source-string is a graphic string, then search-string must be a graphic string, but not a DBCLOB, with an actual length of 16336 double-byte characters or less.
- If source-string is a binary string, then search-string must be a binary string with an actual length of 32672 bytes or less.
If search-string has a length of zero, the result returned by the function is 1.
Otherwise:
- If source-string has a length of zero, the result returned by the function is zero.
- Otherwise:
  - If the value of search-string is equal to an identical length substring of contiguous positions from 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.

Example

Select RECEIVED and SUBJECT columns as well as the starting position of the words 'GOOD BEER' within the NOTE_TEXT column for all entries in the IN_TRAY table that contain these words.

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