SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
P-24
Examples—POSITION Function

If the length of substring is greater than the length of the character string, SQL
returns 0.

If character-string, substring, or occurrence is a null value, SQL returns
a null value.

The collating sequences of substring and character-string must be the
same or comparable, or SQL returns an error. The character sets of substring
and character-string must also be identical.

To ignore case in the search, use the UPSHIFT function or a collation.
Examples—POSITION Function

This example returns the value 8:
POSITION("John" IN "Robert John Smith")

This example returns the value 12, which is the starting position of the second
occurrence of “Hello”:
POSITION("Hello" IN "Hello, and Hello", 2)

This query returns all records in table EMPLOYEE that contain the substring
“Smith” in the EMPNAME column:
SELECT * FROM EMPLOYEE WHERE POSITION("Smith" IN EMPNAME) > 0

This query returns all records in table EMPLOYEE that contain the substring
“SMITH,” regardless of whether the substring is in uppercase or lowercase
characters:
SELECT * FROM EMPLOYEE
WHERE POSITION ("SMITH" IN UPSHIFT(EMPNAME)) > 0
Predicates
A predicate is a statement involving a comparison that evaluates to a value of true,
false, or unknown (null). Use predicates within search conditions to specify criteria for
choosing rows from tables or views. SQL includes this predicates:
BETWEEN Predicate
Comparison Predicate (=, <>, <, >, <=, >=)
EXISTS Predicate
IN Predicate
LIKE Predicate
NULL Predicate
Quantified Predicate (ALL, ANY, SOME)