Firebird Documentation IndexFirebird 2.1 Language Ref. UpdateInternal functions → POSITION()
Firebird Home Firebird Home Prev: PI()Firebird Documentation IndexUp: Internal functionsNext: POWER()

POSITION()

Available in: DSQL, PSQL

Added in: 2.1

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.

Result type: INTEGER

Syntax: 

POSITION (<args>)

<args>  ::=  substr IN string
             | substr, string [, startpos]
  • 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 causes POSITION to always return 1 if substr is the empty string. This is fixed in 2.1.4.

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

Examples: 

position ('be' in 'To be or not to be')      -- returns 4
position ('be', 'To be or not to be')        -- returns 4
position ('be', 'To be or not to be', 4)     -- returns 4
position ('be', 'To be or not to be', 8)     -- returns 17
position ('be', 'To be or not to be', 18)    -- returns 0
position ('be' in 'Alas, poor Yorick!')      -- returns 0

Warning

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.

Prev: PI()Firebird Documentation IndexUp: Internal functionsNext: POWER()
Firebird Documentation IndexFirebird 2.1 Language Ref. UpdateInternal functions → POSITION()