SQL/MX 3.1 Reference Manual (H06.23+, J06.12+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Release 3.1 Reference Manual—663850-001
9-87
Considerations for LOCATE
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-41.
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 the case in the search, use the UCASE function (or the LCASE function) for
both the substring-expression and the source-expression.
Examples of LOCATE
•
Return the value 8 for the position of the substring ‘John’ within the string:
LOCATE ('John','Robert John Smith')
•
Suppose that the EMPLOYEE table has an EMPNAME column that contains both
the first and last names. This SELECT statement returns all records in table
EMPLOYEE that contain the substring 'SMITH', regardless of whether the column
value is in uppercase or lowercase characters:
SELECT * FROM persnl.employee
WHERE LOCATE ('SMITH',UCASE(empname)) > 0 ;