The POSITION function returns the starting position of the first occurrence of one string within another string.

The schema is SYSIBM.

The string the POSITION function searches for is called the search-string.
The string it searches in is called the source-string.
The POSITION function returns the starting position of the first occurrence of one string (called the search-string) within another string (called the source-string).
If search-string is not found and neither argument is null, the result is zero.
If the search-string is found, the result is a number from 1 to the actual length of source-string, expressed in the string unit that is explicitly specified.
The search is done using the collation of the database, unless search-string or source-string is defined as a binary string or as FOR BIT DATA, in which case the search is done using a binary comparison.

If source-string has an actual length of 0, the result of the function is 0.
If search-string has an actual length of 0 and source-string is not null, the result of the function is 1.

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 character string, graphic string, binary string, numeric value, Boolean value, or datetime value.
If the value is not a character string, graphic string, or binary string, it is implicitly cast to VARCHAR before the function is evaluated.
The expression cannot be a LOB file reference variable. The expression can be specified by any of the following elements:

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

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

source-string

An expression that specifies the string to be searched through.
This expression must return a value that is a built-in character string, numeric value, Boolean value, or datetime value.
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 of the following elements:

• 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 that concatenates (using CONCAT or ||) any of the previously listed items

CODEUNITS16 or CODEUNITS32 or OCTETS

Specifies the string unit of the result. CODEUNITS16 specifies that the result is to be expressed in 16-bit UTF-16 code units.
CODEUNITS32 specifies that the result is to be expressed in 32-bit UTF-32 code units.
OCTETS specifies that the result is to be expressed in bytes.

If a string unit is specified as CODEUNITS16 or CODEUNITS32, and search-string or source-string is a binary string or a FOR BIT DATA string, an error is returned (SQLSTATE 428GC).

If a string unit argument is not specified and both search-string and source-string are either a character string that is not FOR BIT DATA or is a graphic string, the default is CODEUNITS32. Otherwise, the default is OCTETS.

If a locale-sensitive UCA-based collation is used for this function, then the CODEUNITS16 option offers the best performance characteristics.

For more information about CODEUNITS16, CODEUNITS32, and OCTETS, see String units in built-in functions in Character strings.

The first and second arguments must have compatible string types.
For more information about compatibility, see Rules for string conversions.
In a Unicode database, if one string argument is character (not FOR BIT DATA) and the other string 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).

Result

The result of the function is a large integer.
If any argument can be null, the result can be null; if any argument is null, the result is the null value.

Examples

1. 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
      ,      POSITION('GOOD BEER', NOTE_TEXT, OCTETS)
      FROM   IN_TRAY
      WHERE  POSITION('GOOD BEER', NOTE_TEXT, OCTETS) <> 0

2. Find the position of the character 'ß' in the string 'Jürgen lives on Hegelstraße', and set the host variable LOCATION with the position, as measured in CODEUNITS32 units, within the string.

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

   The value of host variable LOCATION is set to 26.
3. Find the position of the character 'ß' in the string 'Jürgen lives on Hegelstraße', and set the host variable LOCATION with the position, as measured in OCTETS, within the string.

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

   The value of host variable LOCATION is set to 27.
4. The following examples work with the Unicode string '&N~AB', where '&' is the musical symbol G clef character, and '~' is the non-spacing combining tilde character.
   This string is shown in different Unicode encoding forms in the following example:
   
   

   	'&'	'N'	'~'	'A'	'B'
   UTF-8	X'F09D849E'	X'4E'	X'CC83'	X'41'	X'42'
   UTF-16BE	X'D834DD1E'	X'004E'	X'0303'	X'0041'	X'0042'

   Assume that the variable UTF8_VAR contains the UTF-8 representation of the string.

      SELECT POSITION('N', UTF8_VAR, CODEUNITS16),
             POSITION('N', UTF8_VAR, CODEUNITS32),
             POSITION('N', UTF8_VAR, OCTETS)
      FROM   SYSIBM.SYSDUMMY1

   returns the values 3, 2, and 5, respectively.

   Assume that the variable UTF16_VAR contains the UTF-16BE representation of the string.

   
      SELECT POSITION('B', UTF16_VAR, CODEUNITS16),
             POSITION('B', UTF16_VAR, CODEUNITS32),
             POSITION('B', UTF16_VAR, OCTETS)
      FROM   SYSIBM.SYSDUMMY1

   returns the values 6, 5, and 11, respectively.

5. In a Unicode database created with the case insensitive collation CLDR181_LEN_S1, find the position of the word 'Brown' in the phrase 'The quick brown fox'.

      SET :LOCATION = POSITION('Brown', 'The quick brown fox', CODEUNITS16)

   The value of the host variable LOCATION is set to 11.