>>-+-POSITION--(--search-string--IN--source-string--)-+-------->< '-POSSTR--(--source-string--,--search-string--)----'
The POSITION and POSSTR functions return the starting position of the first occurrence of one string (called the search-string) within another string (called the source-string). If the search-string is not found and neither argument is null, the result is zero. If the search-string is found, the result is a number from 1 to the actual length of the source-string. See the related function, LOCATE.
If either argument is a UTF-8 or UTF-16 string, the query cannot contain:
The result of the function is a large integer. If either of the arguments can be null, the result can be null. If either of the arguments is null, the result is the null value.
The POSITION function operates on a character basis. The POSSTR function operates on a strict byte-count basis. It is recommended that if either the search-string or source-string contains mixed data, POSITION should be used instead of POSSTR. Because POSSTR operates on a strict byte-count basis, if the search-string or source-string contains mixed data, the search-string will only be found if any shift-in and shift-out characters are also found in the source-string in exactly the same positions. Because POSITION operates on a character-string basis, any shift-in and shift-out characters are not required to be in exactly the same position and their only significance is to indicate which characters are SBCS and which characters are DBCS.
If the CCSID of the search-string is different than the CCSID of the source-string, it is converted to the CCSID of the source-string.
If a sort sequence other than *HEX is in effect when the statement that contains the POSSTR or POSITION function is executed and the arguments are SBCS data, mixed data, or Unicode data, then the result is obtained by comparing weighted values for each value in the set. The weighted values are based on the sort sequence. An ICU sort sequence table may not be specified with the POSSTR or POSITION function.
If the search-string has a length of zero, the result returned by the function is 1. Otherwise:
SELECT RECEIVED, SUBJECT, POSSTR(NOTE_TEXT, 'GOOD') FROM IN_TRAY WHERE POSSTR(NOTE_TEXT, 'GOOD') <> 0
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.