POSITION

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, expressed in the units that are explicitly specified.

search-string

An expression that specifies the string for which to search. search-string must return a value that is any built-in 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 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 (although nested function invocations cannot be used)
• An expression that concatenates (using CONCAT or ||) any of the above
• A CAST specification whose arguments are any of the above
• A column name

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

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 any built-in string data type.

The argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type.

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

CODEUNITS16, CODEUNITS32, or OCTETS

Specifies the string unit that is used to express the result. If source-string is a character string that is defined as bit data, CODEUNITS16, or CODEUNITS32 cannot be specified. If source-string is a graphic string, OCTETS cannot be specified.

CODEUNITS16

Specifies that the result is expressed in terms of 16-bit UTF-16 code units.

CODEUNITS32

Specifies that the result is expressed in terms of 32-bit UTF-32 code units.

OCTETS

Specifies that the result is expressed in terms of bytes.

For more information about CODEUNITS16, CODEUNITS32, and OCTETS, see String unit specifications.

The first and second arguments must have compatible string types. For more information on compatibility, see “Conversion rules for operations that combine strings”.

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. If either CODEUNITS16 or CODEUNITS32 is specified, the function might be evaluated on a temporary copy of the data in Unicode.

The strings can contain mixed data. If OCTETS is specified:

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

The result of the function is a large integer. The POSITION function accepts mixed data strings.

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

When the POSITION function is invoked with OCTETS, the function operates on a strict byte-count basis without regard to single-byte or double-byte characters.

If the CCSID of the search string is different than the CCSID of the source string, it is converted to the CCSID of the source string.

The value of the result is determined by applying these rules in the order in which they appear:

• If search-string has a length of zero, the result is 1.
• If source-string has a length of zero, the result is 0.
• If the value of search-string is equal to an identical length of substring of contiguous positions within the value of source-string, the result is the starting position of the first such substring within the source string value.
• Otherwise, the result is 0. This includes the case where search-string is longer than source-string.

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

SET :LOCATION = POSITION('ß','Jürgen lives on Hegelstraße',CODEUNITS32);

SET :LOCATION = POSITION('ß','Jürgen lives on Hegelstraße',OCTETS);