POSITION function

POSITION is a string manipulation function that manipulates all data types (BIT, BLOB, and CHARACTER), and returns the position of one string within another.

Syntax

POSITION returns an integer giving the position of one string (SearchExpression) in a second string (SourceExpression). A position of one corresponds to the first character of the source string.

If present, the FROM clause gives a position within the search string at which the search commences. In the absence of a FROM clause, the source string is searched from the beginning.

If present, the REPEAT clause gives a repeat count, returning the position returned to be that of the nth occurrence of the search string within the source string. If the repeat count is negative, the source string is searched from the position given by the FROM clause, or from the end of the string if the FROM clause is absent.

In the absence of a REPEAT clause, a repeat count of +1 is assumed; that is, the position of the first occurrence, searching from the beginning is returned. If the search string has a length of zero, the result is one.

If the search string cannot be found, the result is zero: if the FROM clause is present, this applies only to the section of the source string being searched; if the REPEAT clause is present this applies only if there are insufficient occurrences of the string.

If any parameter is NULL, the result is NULL.

The search and source strings can be of the CHARACTER, BLOB, or BIT data types but they must be of the same type.

For example:

         POSITION('Village' IN 'Hursley Village');   returns 9
         POSITION('Town' IN 'Hursley Village');   returns 0 

         POSITION ('B' IN 'ABCABCABCABCABC'); -> returns 2
         POSITION ('D' IN 'ABCABCABCABCABC'); -> returns 0

         POSITION ('A' IN 'ABCABCABCABCABC' FROM 4); -> returns 4
         POSITION ('C' IN 'ABCABCABCABCABC' FROM 2); -> returns 3

         POSITION ('B' IN 'ABCABCABCABCABC' REPEAT 2); -> returns 5
         POSITION ('C' IN 'ABCABCABCABCABC' REPEAT 4); -> returns 12

         POSITION ('A' IN 'ABCABCABCABCABC' FROM 4 REPEAT 2); -> returns 7
         POSITION ('AB' IN 'ABCABCABCABCABC' FROM 2 REPEAT 3); -> returns 10

         POSITION ('A' IN 'ABCABCABCABCABC' REPEAT -2); -> returns 10
         POSITION ('BC' IN 'ABCABCABCABCABC' FROM 12 REPEAT -3); -> returns 5