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

SQL/MX Functions and Expressions

HP NonStop SQL/MX Reference Manual—523725-004

9-70

LOCATE Function

The LOCATE 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 LOCATE function is equal to the result

returned by the POSITION function.

LOCATE is an SQL/MX extension.

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

Considerations for LOCATE

Result of LOCATE

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 0. If source-expression is a null value,

NonStop SQL/MX returns a null value.

Using UCASE

To ignore case in the search, use the UCASE function (or the LCASE function) for both

the substring-expression and the source-expression.

LOCATE (substring-expression,source-expression)