POSSTR scalar function

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 must return a value that is a built-in string, numeric, or datetime data type. If the value is not a string data type, it is implicitly cast to VARCHAR before evaluating the function. The expression can be specified by any one of:

• A constant
• A special register
• A global variable
• A host variable (including a locator variable or a file reference variable)
• A scalar function
• A large object locator
• A column name
• An expression that concatenates (using CONCAT or ||) any of the above

search-string

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

The expression must return a value that is a built-in CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, BLOB, numeric, or datetime data type. If the value is not a CHAR, VARCHAR, GRAPHIC, VARGRAPHIC or BLOB data type, it is implicitly cast to VARCHAR before evaluating the function. The actual length must not be greater than maximum length of a VARCHAR. The expression cannot be a BLOB file reference variable. The expression can be specified by any one of:

• A constant
• A special register
• A global variable
• A host variable
• A scalar function whose operands are any of the above
• An expression that concatenates (using CONCAT or ||) any of the above
• An SQL procedure parameter

The following are examples of invalid string expressions or strings:

• SQL user-defined function parameters
• Trigger transition variables
• Local variables in compound SQL (inlined) statements

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

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

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