Show pagesource

Old revisions

QuickLinks	Statements	Functions	OdxSQL

Omnidex SQL Function: POSITION

Description

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.

Syntax

POSITION( search_expression IN target_expression)

search_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.

target_expression

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.

Example

Example 1: Column

> 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

Example 2: Complex expression

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