Function group: numeric function
POSITION() determines the position of a string in another string.
POSITION ( expression IN expression [USING CODE_UNITS])
expression
Alphanumeric expression or national expression. Its evaluation must return either an alphanumeric string (data type CHAR or VARCHAR) or a national string (data type NCHAR or NVARCHAR). expression may not be a multiple value with dimension > 1. See also section "Compatibility between data types".
Result
In the following description of the possible results, string1 is the string whose position is to be determined, and string2 is the other string.
string1 and/or string2 contains the NULL value:
The result is the NULL value.
string1 has the length 0:
The result is 1.
string1 is in string2:
The result is 1 greater than the number of characters (for CHAR/VARCHAR) or code units (for NCHAR/NVARCHAR) of string2 which precede the first character or the first code unit of string1.
Otherwise: The result is 0.
Data type: INTEGER
Examples
Determine the position of the string 'nett' in the string 'annette' (result: 3):
POSITION ('nett' IN 'annette')
Determine the position of the string 'Vogue' (result: 26):
POSITION('Vogue' IN 'If it''s in vogue it''s in Vogue.')
Determine the position of the string 'Puss' in the string 'boots' (result: 0):
POSITION ('Puss' IN 'boots')