SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Reference Manual—523725-004
9-105
POSITION Function
POSITION Function
The POSITION funcNonStop tion searches for a given substring in a character string. If
the substring is found, SQL/MX returns the character position of the substring within
the string. The result returned by the POSITION function is equal to the result returned
by the LOCATE function.
substring-expression
is an SQL character value expression that specifies the substring to search for in
source-expression. The substring-expression cannot be NULL. See
Character Value Expressions on page 6-39.
source-expression
is an SQL character value expression that specifies the source string. The
source-expression cannot be NULL. See Character Value Expressions on
page 6-39.
NonStop SQL/MX returns the result as a 2-byte signed integer with a scale of zero. If
substring-expression is not found in source-expression, NonStop SQL/MX
returns zero.
Considerations for POSITION
Result of POSITION
If the length of source-expression is zero and the length of substring-
expression is greater than zero, NonStop SQL/MX returns 0. If the length of
substring-expression is zero, NonStop SQL/MX returns 1.
If the length of substring-expression is greater than the length of source-
expression, NonStop SQL/MX returns zero. If source-expression is a null value,
NonStop SQL/MX returns a null value.
Using the UPSHIFT Function
To ignore case in the search, use the UPSHIFT function (or the LOWER function) for
both the substring-expression and the source-expression.
POSITION (substring-expression IN source-expression)