SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
P-23
POSITION Function
POSITION Function
The POSITION function searches for a given substring in a character string. If the
substring is found, SQL returns the character position of the substring within the string.
substring
specifies the string substring to search for in character-string.
character-string
specifies the source string.
occurrence
specifies which occurrence of the substring to look for. occurrence must have an
unsigned numeric data type with a scale of zero. The value of occurrence must
be greater than zero; otherwise, SQL returns an error. If you omit occurrence,
SQL searches for the first occurrence of the substring.
Considerations—POSITION Function

The result is returned as a two-byte signed integer with a scale of zero.

If the substring is not found in character-string, SQL returns 0.

If the value of occurrence is greater than the number of occurrences of
substring in the string, SQL returns 0.

If the length of the character string is zero and the length of substring is greater
than zero, SQL returns 0. If the length of substring is zero, SQL returns 1.
POSITION (substring IN character-string[,occurrence])
where substring and character-string are:
{ string-literal }
{ column-name }
{ param-name }
{ host-var-name }
{ UPSHIFT function }
{ character-expression }
and occurrence is:
{ numeric-literal }
{ column-name }
{ param-name }
{ host-var-name }
{ expression }