<< PI() | FB 2.1 Language Reference | 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.

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

back to top of page
<< PI() | FB 2.1 Language Reference | POWER() >>