The POSITION function finds the position of a search_string in a target_string. If the target_string is of length zero, the result is 1. If the search_string occurs as a substring within the target_string, the character position where the search_string first occurs is returned. If neither of these conditions is true, the result is 0.
String comparisons are case-sensitive. Use the UPPER and LOWER functions around the search_string and the target_string to produce a case-insensitive search.
This function returns an INTEGER datatype.
POSITION( search_expression IN target_expression)
Any expression that returns a character-class datatype to be used as the search criteria. Examples of expressions are columns, concatenations of columns, literal strings and functions that return a character datatype.
Any expression that returns a character-class datatype to be used as the search target. Examples of expressions are columns, concatenations of columns, literal strings and functions that return a character datatype.
> select name, position('.' in name) pos from individuals; NAME POS -------------------------------------------------- ----------- MS. MARY LEWIS 3 MRS. ALICE R TOLLIVER 4 MR. SIDNEY K TOLLIVER 3 MRS. JENNIFER J HOPKINS 4 MR. CHARLIE HOPKINS 3 MS. KIMBERLY BLUE 3 KATHLEEN TUCKER 0
The following example removes the salutation from the name.
> select trim( >> substring(NAME >> from position('.' in NAME) + 1 >> for $column_length(NAME) - position('.' in NAME))) nme >> from INDIVIDUALS; NME -------------------------------------------------- MARY LEWIS ALICE R TOLLIVER SIDNEY K TOLLIVER JENNIFER J HOPKINS CHARLIE HOPKINS KIMBERLY BLUE KATHLEEN TUCKER
See also: