8.3.11. POSITION()

Available in

DSQL, PSQL

Syntax

  1.   POSITION (substr IN string)
  2. | POSITION (substr, string [, startpos])
Table 133. POSITION Function Parameters
ParameterDescription

substr

The substring whose position is to be searched for

string

The string which is to be searched

startpos

The position in string where the search is to start

Result type

INTEGER

Description

Returns the (1-based) position of the first occurrence of a substring in a host string. With the optional third argument, the search starts at a given offset, disregarding any matches that may occur earlier in the string. If no match is found, the result is 0.

Notes

  • The optional third argument is only supported in the second syntax (comma syntax).

  • The empty string is considered a substring of every string. Therefore, if substr is '' (empty string) and string is not NULL, the result is:

    • 1 if startpos is not given;

    • startpos if startpos lies within string;

    • 0 if startpos lies beyond the end of string.

    Notice: A bug in Firebird 2.1 - 2.1.3 and 2.5.0 causes POSITION to always return 1 if substr is the empty string. This is fixed in 2.1.4 and 2.5.1.

  • This function fully supports text BLOBs of any size and character set.

Examples

  1. position ('be' in 'To be or not to be')   -- returns 4
  2. position ('be', 'To be or not to be')     -- returns 4
  3. position ('be', 'To be or not to be', 4)  -- returns 4
  4. position ('be', 'To be or not to be', 8)  -- returns 17
  5. position ('be', 'To be or not to be', 18) -- returns 0
  6. position ('be' in 'Alas, poor Yorick!')   -- returns 0

When used on a BLOB, this function may need to load the entire object into memory. This may affect performance if huge BLOBs are involved.

See also

SUBSTRING()