SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Functions and Expressions

HP NonStop SQL/MX Reference Manual—540440-003

9-105

POSITION Function

The POSITION function searches for a given substring in a character string. If the

substring is found, NonStop 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-37.

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-37.

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)