NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
P-23
Considerations—POSITION Function
occurrence
specifies which occurrence of the substring to look for. occurrence must have an
unsigned numeric data type with a scale of zero. The value of occurrence must
be greater than zero; otherwise, SQL returns an error. If you omit occurrence,
SQL searches for the first occurrence of the substring.
Considerations—POSITION Function
•
The result is returned as a two-byte signed integer with a scale of zero.
•
If the substring is not found in character-string, SQL returns 0.
•
If the value of occurrence is greater than the number of occurrences of
substring in the string, SQL returns 0.
•
If the length of the character string is zero and the length of substring is greater
than zero, SQL returns 0. If the length of substring is zero, SQL returns 1.
•
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
•
The following example returns the value 8:
POSITION("John" IN "Robert John Smith")
•
The following example returns the value 12, which is the starting position of the
second occurrence of “Hello”:
POSITION("Hello" IN "Hello, and Hello", 2)
•
The following 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
•
The following 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