SQL Reference

POSSTR

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

The schema is SYSIBM.

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). 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 source string in which the search is to take place.

The expression can be specified by any one of:

• a constant
• a special register
• a host variable (including a locator variable or a file reference variable)
• a scalar function
• a large object locator
• a column name
• an expression concatenating any of the above

search-string

An expression that specifies the string that is to be searched for.

The expression can be specified by any one of:

• a constant
• a special register
• a host variable
• a scalar function whose operands are any of the above
• an expression concatenating any of the above

with the restrictions that:

• No element in the expression can be of type LONG VARCHAR, CLOB, LONG VARGRAPHIC or DBCLOB. In addition, it cannot be a BLOB file reference variable.
• The actual length of search-string cannot be more than 4 000 bytes.

Note that these rules are the same as those for the pattern-expression described in LIKE Predicate.

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 graphic (DBCS) character.

The POSSTR function accepts mixed data strings. However, POSSTR operates on a strict byte-count basis, oblivious to changes between single and multi-byte characters.

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 or LONG VARCHAR, with an actual length of 32 672 bytes or less.
  • If source-string is a graphic string, then search-string must be a graphic string, but not a DBCLOB or LONG VARGRAPHIC, with an actual length of 16 336 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 32 672 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